Pivot Table Formulas

[rburganmckinley]

Hi! I am trying to create a formula within a pivot table that takes the sum of field 1 and devides it by the count of field one. Anyone know how to do that? It doesn't seem to want to let me do the count of a field.

 

[Chris Davison]

I'm assuming asking for the average of field 1 doesn't do what you want ?

(ie where it default to sum of, just change to average in the pivot table setup)

 

[Chris Davison]

if you're trying it from a formula outside the pivot table itself, you should be able to use the =GETPIVOTDATA function :

=GETPIVOTDATA(F3,"John Sum of Amount")/GETPIVOTDATA(F3,"John Count of Amount2")

where F3 is *any* cell within the pivot table and "John Sum of Amount" if the row of John's info where you have summed on his amounts (it could also be something like "North region Total") divided by his corrosponding count of amounts

this formula can be anywhere.... that's the beauty of GETPIVOTDATA, it doesn't have to be on John's row or anything....

 

[Mark W.]

On 2002-09-18 10:27, rburganmckinley wrote:
Hi! I am trying to create a formula within a pivot table that takes the sum of field 1 and devides it by the count of field one. Anyone know how to do that? It doesn't seem to want to let me do the count of a field.

I concur with Chris... it sounds like you want the average of 'field 1' which doesn't require a Calculated Field. Just drag another copy of the 'field 1' button to your PivotTable's DATA area and set its summary function to Average.

 

[rburganmckinley]

Thanks guys! I must have had a MAJOR brain fart, thinking that I had to calculate it when average is doing the same thing.