Pivot Table grand total across columns

Question

I'm using Excel 2010 and Power Pivot. I'm trying to calculate confidence and velocity for a development team. I'm extracting some information from our time and defect system each day and building a data set. What I need to do with Excel is do the calculations. So each day I add to my data set 1 row per task in the current project, estimate for that task and the time spent on that task. What I want to calculate is the estimate/actual for each task but also for each person. The trouble is that each day the actual is cumulative so I need to pick out the maximum value for each task. The estimate should remain unchanged. I can make this work at the task level with a calculated measure (=MAX(worked)/MAX(estimate)) but I don't know how to total this up for a person. I need the sum of the max worked for each task. So a dataset might look like:

Name    Task    Estimate     Worked
N1       T1        3             1
N2       T2        3             1
N3       T3        4             1
N1       T1        3             2
N2       T4        5             1
N3       T3        4             2
N1       T5        1             2
N2       T6        2             3
N3       T7        3             2

What I want to see is for task T1 2 days were worked against an estimate of 3 days - so 2/3. For person N1 I want to see that they worked a total of 4 days against an estimate of 4 days so 4/4. For person N2 they worked 5 days for an estimate of 10 days. Any ideas on how I can achieve this?

Answer 1

You can create a MAX-IF statement for each person:

=MAX(IF(A2:A10="N2", D2:D10))

This will select only the rows where the name is "N2" and then apply the MAX function to those cells.

NOTE: You will need to hit Ctrl-Shift-Enter to get Excel to execute this formula otherwise you will get a #VALUE error in the cell.