calculated fields in pivot tables

Juiceandbran

New Member
Joined
Sep 4, 2006
Messages
3
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Explain what the weighting should be. Isn't your data per employee?
 

Juiceandbran

New Member
Joined
Sep 4, 2006
Messages
3
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
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
It works fine for me when I have a calculated field for rate. Is yours a calculated field, or not?
 

Juiceandbran

New Member
Joined
Sep 4, 2006
Messages
3
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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?
 

Forum statistics

Threads
1,141,731
Messages
5,708,151
Members
421,549
Latest member
Dtcfire

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?

Disable AdBlock

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
Top