Pivot table calculated fields

Arb

New Member
Joined
Oct 14, 2006
Messages
3
Hello

Hope somebody can point me in the right direction. I have created a pivot table that organizes project data. There is a field Project time which contain the amount of time booked to a particular project and by who. I have created a calculated field =IF('Project Time'>=25, 1, 0) this produces a 1 for people who have booked more than 25 hours to a particular project. However this field isn’t totaled like other calculated fields. Under the total I just get 1. Am I missing something or can anybody suggest an alternative solution.
This pivot table is run from a VBA macro and analyses weekly data, I can’t add the formula to the source because this distorts the summary data .
Regards and thanks
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

mrhartley

Well-known Member
Joined
Mar 8, 2005
Messages
564
Hi and welcome to the board.

I dont know but it might be that your formula is working correctly in the Grand Total too.

So instead of Grand Total, totalling all the 1's in your calculated field it might be that the Grand Total is also doing for example: =IF(Grand Total'Project Time'>=25, 1, 0) and since the Grand Total of Project Time is =>25 then the resulting figure = 1

I don't know if thats true or if that makes sense. I would be surprised if that is true failing any other ideas...

Here are a couple of links to other bits if input:
http://www.mrexcel.com/archive/Pivot/4588.html
http://support.microsoft.com/kb/211470/en-us

Regards
~Mark
 

Arb

New Member
Joined
Oct 14, 2006
Messages
3
Thanks I agree with what your saying I have since added another field to the source data.

However for future reference is there a way to change how the totals are calculated

Cheers
 

mrhartley

Well-known Member
Joined
Mar 8, 2005
Messages
564
I dont think it's possible to change the way the grand totals are calculated.

I think too that the best way forward is to do what you already know, add the calculation to the source data.

Good luck
~Mark
 

Forum statistics

Threads
1,136,518
Messages
5,676,324
Members
419,619
Latest member
jalme

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