Excel % inc/dec with a zero value

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:27 PM GMT

Question

I need the % increase or decrease for prior year to day to current year to date however when I enter the formula =IF(C7>0,(B7-C7)/C7,(B7-C7)/C7*-1) I get a formula error for every entry that has a 0 (zero) value for PYTD column. What formula do I need so that I get a true increase in my % Inc/Dec column?


CYTD PYTD % Inc/Dec

15,741 0 #DIV/0!


Answer

Jessica

you can't calculate a % increase or decrease from zero. > I get a true increase in my % Inc/Dec column what do you think that true increase is. If you have a rule, then you can probably incorporate that in your formula - but I don't know of any such accepted rule.

I don't know what you want to show in that case, but you can test for the condition and avoid the #Div/0! error

=If(or(C7=0,C7=""),"",IF(C7>0,(B7-C7)/C7,(B7-C7)/C7*-1))

Advertisement

©2020 eLuminary LLC. All rights reserved.