# Pivot Table Formulas

#### rburganmckinley

##### New Member
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.

### Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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)

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....

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.

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

Replies
0
Views
159
Replies
0
Views
114
Replies
5
Views
351
Replies
0
Views
156
Replies
12
Views
354

1,196,516
Messages
6,015,664
Members
441,914
Latest member
VBAllTheThings

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back