Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
    
that's what I want to do but I'm not sure how to put that into a pivot table as a measure. Basically, each day there might be additional tasks or names so I want to do this in a way that will automatically react to that. –  Jon Jun 8 '12 at 15:30
    
Short of writing some functions in code I am not sure how to do in a pivot table. –  Brad Patton Jun 12 '12 at 1:06
    
yeah I've come to the same conclusion. Since I'm pulling in data programatically anyway I'll update my app to spit out the information in a more useful manner. –  Jon Jun 14 '12 at 12:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.