# calculated fields in pivot tables

#### Juiceandbran

##### New Member
I have a table with wage data per employee with dollars, hours and average hourly rate. The pivot table gives me an average hourly rate, not a weighted average. How do I get this?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Explain what the weighting should be. Isn't your data per employee?

pivot table

The average should be the total of dollars paid divided by total hours worked, not the average of the hourly rates at an individual level

div section employee time dollars hours average rate
1 23 smith ordinary 1500 100 15.00
1 23 jones ordinary 8000 400 20.00
Total 9500 500 17.50
Weighted average 19.00

It works fine for me when I have a calculated field for rate. Is yours a calculated field, or not?

Pivot tables calculated fields

Hi Glenn,
That is my problem, I cannot create a calculated field. When I right click on a cell in the pivot table, I get to see the formula option, but there are no options to see under formula. I am sure I have done this before and been able to divide total dollars by total hours to get the weighted average, but not this time.

That sounds very strange:
... but there are no options to see under formula

Are there options there that are greyed-out? Or will it just not show any options?

Replies
6
Views
161
Replies
2
Views
220
Replies
1
Views
408
Replies
0
Views
196
Replies
1
Views
266

1,219,570
Messages
6,149,044
Members
450,853
Latest member
xtiinctt

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

### Which adblocker are you using?

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

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