I have a worksheet which, among others, has 3 columns of different type changes, plus a column of the totals of those 3 columns (derived by a different methodology). I'm doing a pivot on these values, so in my VALUES section I have the 4 columns. I'd like to add a check that the 3 columns add to the value of the 4th column. I'm sure I need to do this as a calculated field, but not sure how to go about it. I've attached a little mockup of what I'm wanting to do. Any help would be appreciated.
If that's your desired destination... that's not the way I'd go to get there. there are 5 formulae here that will keep your data up to date (unlike Pivot Tables). I used to be a huge fan of PTs, but rarely use them anymore...
Last edited by Glenn Kennedy; 03-27-2017 at 03:40 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi,
You need two calculated fields.
Select a cell in the pivot table and on the PivotTable Tools - Options tab, click the 'Fields, Items and Sets' dropdown and select Calculated Field.
Choose a name and use the formula: ='GR Restate' +'Fed Change' +'PAC Trnsfr'
Then add another field using the formula ='Bud Diff to Load' -Col4 (where col4 is the field you added first of all)
Finally position the fields wherever you like.
Don
Please remember to mark your thread 'Solved' when appropriate.
Glenn,
I also thought of the Sumifs solutions, but with this need there are massive amounts of data, so I'm not sure that SumIFs isn't going to bog me down. But all in all I agree with you on the unnessecity of pivot tables.
xlnitwit,
Thanks for making it so easy. Calculated fields in Pivot Tables is one of two major Excel concepts I've yet to wrap my head around (the other, that throws me every time, is how to make a formula for Conditional Formatting and get to start at the right place. I just don't understand the logic there. I know I'm not stupid, but I suspect I'm a lot stupider than I think I am).
You're welcome.Originally Posted by jomili
I think that is true for most of us!I know I'm not stupid, but I suspect I'm a lot stupider than I think I am).
All of us... and getting worse as the years go by...
You are cool guys)
Have a good day.
Thanks. Don't know about cool, just endeavoring to be honest.
I know I'm not stupid, but I suspect I'm a lot stupider than I think I am
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
jomili 
Register To Reply
Bookmarks