Hello,
I have a Pivot table and I need to calculate percent change between two columns, showing result in third one.
I have made a Calculated Item and works ok, except in subtotals and gandtotals. There it gives the error div/0, although the subtotals which i want to compare are more than zero.
How can i solve it?
Thanks
Hi,
Can you post a workbook with a small, mocked-up example?
Regards
Yes, of course. There is a simple example of table, where there are errors in cells where perantage of subtotals should be calculated (procent of gain/price) Below is the way I would like it to look like.
Thanks
Thanks,
You have some entries for which the 'plan' entry is zero, e.g. January|West|store 1|glasses, which causes a #DIV/0! result. This error is then passed on to the subtotals/totals.
Regards
Last edited by XOR LX; 02-10-2022 at 04:11 PM.
Yes, but it is the case in reality, with real datas.
Thanks
You're not going to be able to do this with a Calculated Item.
If you add two additional columns to your source data, called 'Sold' and 'Plan', in columns H and I respectively, with these formulas:
H2 (Sold):
=IF(A2="sold",D2,0)
and copied down.
I2 (Plan):
=IF(A2="plan",D2,0)
and copied down.
Then adjust the Pivot Table source data so that it includes these two new columns. After which you can create a Calculated Field (not Item) with the formula:
=Sold/Plan
Regards
Hello,
I am so sorry that I have to write again.
I cant use that formula which you have wrote. There is message that something is wrong with it.
Thank you very much.
Replace commas with semi-colons for your locale:
=IF(A2="sold";D2;0)
=IF(A2="plan";D2;0)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you very much,
Best regards!
Last edited by AliGW; 02-11-2022 at 07:30 AM. Reason: PLEASE don't quote unnecessarily!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks