Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Subtotals - Calculated field subtotal incorrect
I have a pivot that has the following:
I have 2 calculated fields for the last 2 columns The subtotal for the last column is calculating incorrectly: Activity Agent Std/Hr Vol Time CalcProd/Hr Calc%toStd Presort John 12 25 2 Hrs (25/2)=12.5 (12.5/12)=104% Presort Jane 12 55 3 Hrs (55/3)=18.3 (18.3/12)=152% Subtotal 12 80 5 (80/5)=16 XXX Please advise as to how to make the subtotal for the "Calc%toStd" calculate correctly. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Subtotals - Calculated field subtotal incorrect
reddy wrote:
I have a pivot that has the following: I have 2 calculated fields for the last 2 columns The subtotal for the last column is calculating incorrectly: Activity Agent Std/Hr Vol Time CalcProd/Hr Calc%toStd Presort John 12 25 2 Hrs (25/2)=12.5 (12.5/12)=104% Presort Jane 12 55 3 Hrs (55/3)=18.3 (18.3/12)=152% Subtotal 12 80 5 (80/5)=16 XXX Please advise as to how to make the subtotal for the "Calc%toStd" calculate correctly. Hi reddy, You can get into trouble if a calculated field uses aggregated calculations. Does your formula for Calc%toStd refer to Std/Hr, Vol, and Time (should be good), or Std/Hr and CalcProd/Hr (bad)? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Subtotals - Calculated field subtotal incorrect
The formula for Calc%toStd refers to Std/Hr and CalcProd/Hr. I am guessing
this is not possible huh? "smartin" wrote: reddy wrote: I have a pivot that has the following: I have 2 calculated fields for the last 2 columns The subtotal for the last column is calculating incorrectly: Activity Agent Std/Hr Vol Time CalcProd/Hr Calc%toStd Presort John 12 25 2 Hrs (25/2)=12.5 (12.5/12)=104% Presort Jane 12 55 3 Hrs (55/3)=18.3 (18.3/12)=152% Subtotal 12 80 5 (80/5)=16 XXX Please advise as to how to make the subtotal for the "Calc%toStd" calculate correctly. Hi reddy, You can get into trouble if a calculated field uses aggregated calculations. Does your formula for Calc%toStd refer to Std/Hr, Vol, and Time (should be good), or Std/Hr and CalcProd/Hr (bad)? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Subtotals - Calculated field subtotal incorrect
reddy wrote:
The formula for Calc%toStd refers to Std/Hr and CalcProd/Hr. I am guessing this is not possible huh? "smartin" wrote: reddy wrote: I have a pivot that has the following: I have 2 calculated fields for the last 2 columns The subtotal for the last column is calculating incorrectly: Activity Agent Std/Hr Vol Time CalcProd/Hr Calc%toStd Presort John 12 25 2 Hrs (25/2)=12.5 (12.5/12)=104% Presort Jane 12 55 3 Hrs (55/3)=18.3 (18.3/12)=152% Subtotal 12 80 5 (80/5)=16 XXX Please advise as to how to make the subtotal for the "Calc%toStd" calculate correctly. Hi reddy, You can get into trouble if a calculated field uses aggregated calculations. Does your formula for Calc%toStd refer to Std/Hr, Vol, and Time (should be good), or Std/Hr and CalcProd/Hr (bad)? You are not necessarily stuck. As long as you have Std/Hr, Vol, and Time available, you can create valid CalcProd/Hr and Calc%toStd: CalcProd/Hr = Vol / Time {fine; you did this already} Calc%toStd = [CalcProd/Hr] / [Std/Hr] {don't do this--based on aggregate} = (Vol / Time) / [Std/Hr] {do this} Just make sure you base your forumlae on /unaggregated data/ instead of the aggregated field [CalcProd/Hr] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Blank SubTotal in Calculated Field | Excel Worksheet Functions | |||
Incorrect total of calculated field in pivot table | Excel Discussion (Misc queries) | |||
Pivot table formula (calculated field) result is incorrect | Excel Worksheet Functions | |||
Pivot table calculated field and subtotal | Excel Worksheet Functions | |||
Totals of calculated field in pivot table give incorrect results | Excel Worksheet Functions |