12

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Bas
  • 597
  • 5
  • 10
  • 22
  • 1
    Meta question: Are Excel power-user questions such as this best suited for StackOverflow, SuperUsers, or some other StackExchange site? – StockB Oct 18 '13 at 12:31

3 Answers3

9

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.

Jonathan Drapeau
  • 2,610
  • 2
  • 26
  • 32
ThreeTrickPony
  • 196
  • 2
  • 3
  • This is a nice workaround, but it doesn't work in other situations. You cannot do a standard deviation easily, for example. I have ended up using powerquery. – jabellcu Aug 27 '21 at 09:12
4

You need to refer to the pivot table data in your formula, something like this:

=GETPIVOTDATA("Sum of Amount",$A$3,"Product","A")/GETPIVOTDATA("Average of Interval",$A$3,"Product","A")

Edit: From your spreadsheet: To add your desired column to Pivot Table A:

K5=GETPIVOTDATA("Sum of Amount",$H$2,"Month",DATE(2012,5,1),"Product","Prod A")/GETPIVOTDATA("Average of Interval",$H$2,"Month",DATE(2012,5,1),"Product","Prod A")
K6=GETPIVOTDATA("Sum of Amount",$H$2,"Month",DATE(2012,5,1),"Product","Prod B")/GETPIVOTDATA("Average of Interval",$H$2,"Month",DATE(2012,5,1),"Product","Prod B")
K7=GETPIVOTDATA("Sum of Amount",$H$2,"Month",DATE(2012,5,1),"Product","Prod C")/GETPIVOTDATA("Average of Interval",$H$2,"Month",DATE(2012,5,1),"Product","Prod C")

You can also produce the column by adding an extra column to the original data table of: Amount/Sec for each individual entry then when you pivot all the data the product of that column will be your desired result.

Edit (2):

The formulas above are cell formulas not pivot table formulas which can't use references sorry I didn't make that clear. I am looking into a pivot table calculation formula but for now the above as a column alongside your pivot table should produce what you need.

Alistair Weir
  • 1,809
  • 6
  • 26
  • 47
  • can you create this formula based on this example Excel sheet: http://www.tempfiles.net/download/201209/262303/Voorbeeld.html. Pivot A is just a pivot of the 'Originele tabel', Pivot B is the wrong outcome, pivot C is pivot plus the desired outcome added by hand. Can you add your solution to Pivot table C? – Bas Sep 13 '12 at 11:49
  • Ok opened up and added specific formulas based upon Pivot Table A. – Alistair Weir Sep 13 '12 at 13:23
  • 5
    I'm impressed and disappointed by Excel's pivot table support all at once. On the one hand, it's a powerful tool for quickly grouping and displaying information. On the other hand, it's hamstrung by odd limitations like not being able to base calculated fields on value types other than sums, such as averages or counts, even though they are displayed in the pivot table. Why can't I reference my other pivot table columns in calculated fields? – StockB Oct 18 '13 at 12:58
0

make a field in the source (counter) and put "1" in every line. put it also into the pivot and divide the SUM of seconds with this field - then it Will be an average. divide the original field with this average so your final-final division Will be the amount per sum of seconds per counter and that Will make the trick :)

Viktor Lippai
  • 1