Calculated Field - Max # divided by a Sum

[lpeacock]

Hello,

I am trying to create a calculated field in a pivot table (using Excel 2010), whereby the Max of a given field is divided by the Sum for a different field.

My dataset includes a history of projects, with multiple expected timeframes for completion. I am retrieving the maximum amount of time for a given project, and dividing it by the sum of the actual hours logged.

So, I will have:

Column Headings:
Project Name / Estimated Hours of Completion / Actual Hours Logged

Data:
Project A / 150 / 5
Project A / 150 / 3
Project A / 150 / 1

I do not want to sum the estimated hours of completion because they are the same, which is why I want to retrieve a max number, however I do want to sum the actual hours logged. I want the calculated field to show the percentage of the estimated hours of completion over the actual hours logged.

Is there any way to have this type of calculated field, or am I forced to put the formula next to the pivot table?

Many thanks in advance for any help anyone can provide!

 

[bmorro]

lpeacock,

you can insert a calculated field using the formula =Estimated Hours of Completion/Actual Hours Logged. this will give you a percentage figure which you can format for percentages.

In theory, you should have only one Estimated Hours of Completion for each Project so I don't know why you would want to pull the max for each line item

 

[lpeacock]

Thanks for your reply, but unfortunately I've tried that and it doesn't work.

My raw data lists out each actual hour completed with the estimated hours of completion for each entry, so I will always have multiple values. This is why I've done a "max' on my pivot table, not a sum.

I tried what you have specified, but it is returning the percentage of SUMMED estimated hours of completion over the actual hours. I've tried manually typing in the calculated field formula as "=Max of "Estimated Hours of Completion" but this does not work.

Using my example above, I wish to show in my Pivot table:

Row / Max of Estimated Hours / Sum of Actual Hours / *Calculated Field*
Project A / 150 / 9 / 6.0%

Instead, it shows:

Project A / 150 / 9 / 2.0% (Because it is summing the estimated hours)

 

[bmorro]

Thanks for your reply, but unfortunately I've tried that and it doesn't work.

My raw data lists out each actual hour completed with the estimated hours of completion for each entry, so I will always have multiple values. This is why I've done a "max' on my pivot table, not a sum.

I tried what you have specified, but it is returning the percentage of SUMMED estimated hours of completion over the actual hours. I've tried manually typing in the calculated field formula as "=Max of "Estimated Hours of Completion" but this does not work.

Using my example above, I wish to show in my Pivot table:

Row / Max of Estimated Hours / Sum of Actual Hours / *Calculated Field*
Project A / 150 / 9 / 6.0%

Instead, it shows:

Project A / 150 / 9 / 2.0% (Because it is summing the estimated hours)

I see what you are saying now, lpeacock.

Try creating a helper column within your dataset. For each line use a SUMIF statement to total the Actual Hours worked for each project. When you do your pivot table for the respective project, adjust the calculated field to include the helper column and not the actual hours worked.