Evaluating division by zero
 

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

Evaluating division by zero
 

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(D20,C2/D2,"")
and don't do the division unless you did.
Again - to get a 100% result:
=IF(D20,C2/D2,1)
Hope this helps...

"Shams" wrote:

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

Evaluating division by zero
 

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.

Evaluating division by zero
 

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

"BoniM" wrote:

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(D20,C2/D2,"")
and don't do the division unless you did.
Again - to get a 100% result:
=IF(D20,C2/D2,1)
Hope this helps...

"Shams" wrote:

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

Evaluating division by zero
 

=IF(AND(previous_cost<0,current_cost<0),Your_per centage_formula,Whatever_you_want_when_one_of_the_ costs_is_0)

Tyro

"Shams" wrote in message
...
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

Evaluating division by zero
 

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.....

"Tyro" wrote:


=IF(AND(previous_cost<0,current_cost<0),Your_per centage_formula,Whatever_you_want_when_one_of_the_ costs_is_0)

Tyro

"Shams" wrote in message
...
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

Evaluating division by zero
 

=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.


"Shams" wrote:

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

"BoniM" wrote:

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(D20,C2/D2,"")
and don't do the division unless you did.
Again - to get a 100% result:
=IF(D20,C2/D2,1)
Hope this helps...

"Shams" wrote:

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

Evaluating division by zero
 

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" wrote:

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

"BoniM" wrote:

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(D20,C2/D2,"")
and don't do the division unless you did.
Again - to get a 100% result:
=IF(D20,C2/D2,1)
Hope this helps...

"Shams" wrote:

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

Evaluating division by zero
 

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" wrote:

=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.


"Shams" wrote:

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

"BoniM" wrote:

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(D20,C2/D2,"")
and don't do the division unless you did.
Again - to get a 100% result:
=IF(D20,C2/D2,1)
Hope this helps...

"Shams" wrote:

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

Evaluating division by zero
 

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.


"Shams" wrote:

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" wrote:

=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.


"Shams" wrote:

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

"BoniM" wrote:

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(D20,C2/D2,"")
and don't do the division unless you did.
Again - to get a 100% result:
=IF(D20,C2/D2,1)
Hope this helps...

"Shams" wrote:

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

Evaluating division by zero
 

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

"Shams" wrote in message
...
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.....

"Tyro" wrote:


=IF(AND(previous_cost<0,current_cost<0),Your_per centage_formula,Whatever_you_want_when_one_of_the_ costs_is_0)

Tyro

"Shams" wrote in message
...
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