Evaluating division by zero

[Shams]

Folks,
I am simply trying to calculate % Change in Cost b/w two periods. Now, we
may have introduced a new product in the new period which would have zero
cost in the prior period. Therefore, a simple % change calculation for this
product will yield a #DIV/0! error.

I want to be able to copy down a formula that will first evaluate for
ISerror conditions by inputting a zero value and secondly evaluate for all
others by just doing a % change. I am thinking of combining if(iserror(..
with nested if functions...just not sure about the syntax. Any help is
greatly appreciated

 

[BoniM]

Using the iserror function:
=IF(ISERROR(C2/D2),"",C2/D2)
with C2 being your prior years cost, and D2 this years. This will return a
blank cell for new products. You could have it return 100% if you changed ""
to 1.
You could also simply check to see if you had a prior year cost:
=IF(D2>0,C2/D2,"")
and don't do the division unless you did.
Again - to get a 100% result:
=IF(D2>0,C2/D2,1)
Hope this helps...

 

[Dave]

Hi
One way you could do this.
Let's say "%Formula" is the formula you are currently using to calculate %.
Replace your current formula with:
IF(ISERROR(%Formula),0,%Formula)
Regards - Dave.

 

[Shams]

Hi,
Thanks for your reply. That's what I did exactly..the IsERROR
function...but i realized something else....we may have discontinued a
product in this year..so no cost in current year but $$ in prior year...now a
formula that evaluates (curr Yr/Prior Year)-1 will return -100%..how do i
also avoid this within the same formula? i.e. evaluate zero if prior period
is zero and also evaluate to zero if curr. period is zero...

I thought, I could do an IF(or function such as:
IF(or(af1=0,aj1=0),0,((aj1/af1)-1)))
but this is not working

Any tips is appreciated

 

[Tyro]

=IF(AND(previous_cost<>0,current_cost<>0),Your_percentage_formula,Whatever_you_want_when_one_of_the_costs_is_0)

Tyro

 

[Shams]

Hi Tyro,
I did the following syntax:
=IF(AND(AF48<>0,AJ48<>0),((AJ48/AF48)-1),0)...this seems to work when my
current period is zero..
however, returns DIV/0 when prior period is zero

does it need to be tweaked up a little bit more? Thanks.....

 

[BoniM]

=IF(OR(C2=0,D2=0),0,D2/C2)
If C2 and D2 are current year and prior year costs and either one of them
are zero, then the formula will return a zero, other wise it will divide
current year by prior year.

 

[BoniM]

I think I didn't read far enough before replying... sorry! How is it not
working? Error message, wrong answer? What do you have in the cell when
there is not a previous or current year value?

 

[Shams]

Hi BoniM,
Thanks for your prompt replies! Still a bit of an issue..i copied down your
formula like this: IF(OR(AF10=0,AJ10=0),0,((AJ10/AF10)-1)) where AF10 is
prior period costs and AJ10 is curr. period costs.

Now, this seems to be returning 0 when the curr. period is zero (which is
good) but is returning DIV# when the prior period is zero...somehow it seems
to be ignoring the first OR condition where AF10 is zero....

 

[BoniM]

I cannot duplicate your error -
=IF(OR(AF2=0,AJ2=0),0,AF2/AJ2-1)
works for me with any combination of numbers or lack thereof.
If you would like to send me a copy so I can see the error...
send to boni at msn dot com.

 

[Tyro]

Your formula could be more simply expressed as
=IF(AND(AF48<>0,AJ48<>0),AJ48/AF48-1,0) as division has precedence over
subtraction.

Assuming AF48 is your previous cost and AJ48 is your current cost, the only
way a #DIV/0 error could occur is by having AF48 = 0, even having a previous
cost as low as 1E-307, Excel's smallest positive non-zero number, will not
produce #DIV/0. But, if AF48 is exactly 0, the division will not be done.
Something doesn't seem right here. Do a simple test in the row where the
error is occurring:: =AF48=0 and see if that returns TRUE or FALSE
Also, look at the formula in the cell producing the #DIV/0 error to make
sure it is the right formula.

Tyro