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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top