Correct Grand Total in Pivot Using Calculated Fiel

Question

I have an excel pivot table which looks like

Date        Sum of DURATION   Capped Daily Total
03-Jan                  9.5                  8.0
04-Jan                  6.0                  6.0
05-Jan                 10.5                  8.0
06-Jan                  4.5                  4.5
Grand Total            30.5                  8.0

As you can see, the Grand Total for Capped Daily Total is incorrect. In this example:

1. Date column is added as a Row and comes from source data. It has been Grouped By: Days
2. Sum of DURATION is added in the Values section and comes from the source data.
3. Capped Daily Total is a computed field. The formula used is = MIN('DURATION', 8)

When calculating the grand total it appears that excel has computed the Grand Total and then used the formula = MIN('DURATION (HOURS)', 8) to cap it to 8 hours. I don't want the formula to apply to the Grand Total - only to the rows. The grand total should be a simple SUM formula.

How can I achieve this requirement in Excel?

Answer 1

This is a issue with pivot tables see link. To get around this you can add the Daily Capped Total formula to you table itself instead of the pivot table and the field will calculate correctly.

Another solution to get the total would be to place a formula outside the pivottable and sum the column minus the grand total.

=SUM(G:G)-GETPIVOTDATA("Sum of Daily Capped Total",$E$1)

Based off you description of the raw data you could use this formula. It looks for the first occurrence of the date in the range and if it matches the current row number it will sum and min to get your requirement.

=IF(MATCH(A2,$A$2:$A$15,0)=ROW()-1,MIN(SUMIF($A$2:$A$15,A2,$B$2:$B$15),8),0)