Percent change of 100% to show 100% in pivot table not DIV/0

[Kyle775]

Hi there, i want to make a calculation in a pivot table where a percent difference is calculated by year. The % difference from calculation does not show an increase from the previous year as 100% but a DIV/0 error. Can i make a custom formula that will use the year base field? or something like this?

 

[Kyle775]

THANKS for the repsonse!

i understand the reason i get the error. i am interested in showing an increase of 100% when the year prior to the current year is zero.
I need to calculate increases and often a "category" will be introduced and sales should show 100% sales increase and not DIV/0
Any way to have pivot table handle this?

 

[Richard Schollar]

I think you'd need to do the calculation outside of the pivot table - ie so the percentage calculation is doen in an adjacent cell to the pivot table so that you can use an IF statement to check for a 0 value in the prior year and then to substitue 100% if that is the case).

The obvious problem with this approach though is that the formula is then independent of the pivot table (so won't automatically resize with increasing pivot table rows).

 

[Kyle775]

Thanks again
Thats what i was afraid of.
Access here i come...

 

[turtsmurt]

I found that I just use the % of difference from option in the pivot table. In order to get the 100% of change, I change the pivot table options for errors to reflect a 1. Then I use conditional formatting with a formula such as if RC[-1]+RC[-3]=0, then format font to white so that it's not visible.

That way I get 100% if there is no revenue from the previous year, -100% if there is no revenue in the current year and where there is a 0% of change, I just get a blank cell.

I figure it easier for the boss to read it that way and I don't really ever have to add more calculated fields and everything updates each month without any hitches.

Hope this helps.

Christine