Pivot Calculated formula: SUM(Field1)/AVG(Field2)
I've a simple table with some amount and interval in sec by date and product name.
Month | Product | Amount | Interval in sec
------------------------------------------
05-'12| Prod A | 10 | 5
05-'12| Prod A | 3 | 5
05-'12| Prod B | 4 | 5
05-'12| Prod C | 13 | 5
05-'12| Prod C | 5 | 5
From this table I've derived a PivotTable with SUM(Amount), AVERAGE(Interval in sec) by Month and Product.
Month | Product | SUM of Amount | AVG of Interval in sec
--------------------------------------------------------
05-'12| Prod A | 13 | 5
05-'12| Prod B | 4 | 5
05-'12| Prod C | 18 | 5
So far so good. Now I want to add and extra column to my PivotTable with gives me the outcome of SUM of Amount / AVG of Interval in sec.
Adding a calculated value =SUM(Amount)/AVERAGE(Interval) is not giving me the right values. Excel gives me:
Month | Product | SUM of Amount | AVG of Interval in sec | Amount per sec
-------------------------------------------------------------------------
05-'12| Prod A | 13 | 5 | 1.3
05-'12| Prod B | 4 | 5 | 0.8
05-'12| Prod C | 18 | 5 | 1.8
What it actually is doing is =SUM(Amount)/SUM(Interval in sec) for every Month and Product based on the values in the first table. But I'm looking for:
Month | Product | SUM of Amount | AVG of Interval in sec | Amount per sec
-------------------------------------------------------------------------
05-'12| Prod A | 13 | 5 | 2.6
05-'12| Prod B | 4 | 5 | 0.8
05-'12| Prod C | 18 | 5 | 3.6
So literally divide pivot field 'Sum of Amount' by pivot field 'AVG of Interval in sec'.
How to achieve this?
Answer
In case anyone else comes across this problem, a solution that I've found is to add a helper "Count" column to your data set, where each record has a 1 entered under the "Count" field.
Then, in order to achieve the effect of an Average, you can use:
SUM(FIELD_TO_AVERAGE)/SUM(COUNT)
I believe this should work in all cases where an average is needed, but I haven't tested it very broadly.