formula for spreadsheet, avoiding zeros?
 

i have a row of formulas.
b11 through h11
b8/b9, c8/c9,d8/d9, so on and so forth.
how to i prevent the formula from reporting #DIV/0! when in column B there
isn't a number, or the number is 0.

In these cases I just want it to be either blank, or a 0, not this error.
I know you can't divide by zero, i just don't know how to tell the prog that
it doesn't have to, and to just ignore

formula for spreadsheet, avoiding zeros?
 

Hours 5
Tips 23
Odom1 98776
Odom2 99555
Miles odom2-odom1
Gallons 2.000
Gas $ 3.799
MPG #VALUE!

if I don't work on this day, the numbers are zero.
both gallons (B9) and miles(b8)
i guess what i need is an IF statement that is something like t his:
=IF(B8=0,0,B8/B9) OR IF(B9=0,0,B8/B9)
but that isn't correct.
i just need help with the format.
thanks :)

"spezticle" wrote:

i have a row of formulas.
b11 through h11
b8/b9, c8/c9,d8/d9, so on and so forth.
how to i prevent the formula from reporting #DIV/0! when in column B there
isn't a number, or the number is 0.

In these cases I just want it to be either blank, or a 0, not this error.
I know you can't divide by zero, i just don't know how to tell the prog that
it doesn't have to, and to just ignore

formula for spreadsheet, avoiding zeros?
 

=IF(B9,B8/B9,0)


"spezticle" wrote:

Hours 5
Tips 23
Odom1 98776
Odom2 99555
Miles odom2-odom1
Gallons 2.000
Gas $ 3.799
MPG #VALUE!

if I don't work on this day, the numbers are zero.
both gallons (B9) and miles(b8)
i guess what i need is an IF statement that is something like t his:
=IF(B8=0,0,B8/B9) OR IF(B9=0,0,B8/B9)
but that isn't correct.
i just need help with the format.
thanks :)

"spezticle" wrote:

i have a row of formulas.
b11 through h11
b8/b9, c8/c9,d8/d9, so on and so forth.
how to i prevent the formula from reporting #DIV/0! when in column B there
isn't a number, or the number is 0.

In these cases I just want it to be either blank, or a 0, not this error.
I know you can't divide by zero, i just don't know how to tell the prog that
it doesn't have to, and to just ignore

formula for spreadsheet, avoiding zeros?
 

thanks much!
works seemlessly :)

"Teethless mama" wrote:

=IF(B9,B8/B9,0)


"spezticle" wrote:

Hours 5
Tips 23
Odom1 98776
Odom2 99555
Miles odom2-odom1
Gallons 2.000
Gas $ 3.799
MPG #VALUE!

if I don't work on this day, the numbers are zero.
both gallons (B9) and miles(b8)
i guess what i need is an IF statement that is something like t his:
=IF(B8=0,0,B8/B9) OR IF(B9=0,0,B8/B9)
but that isn't correct.
i just need help with the format.
thanks :)

"spezticle" wrote:

i have a row of formulas.
b11 through h11
b8/b9, c8/c9,d8/d9, so on and so forth.
how to i prevent the formula from reporting #DIV/0! when in column B there
isn't a number, or the number is 0.

In these cases I just want it to be either blank, or a 0, not this error.
I know you can't divide by zero, i just don't know how to tell the prog that
it doesn't have to, and to just ignore

formula for spreadsheet, avoiding zeros?
 

=IF(B9=0,"",B8/B9) will do the trick in this case because it only matters
if B9 is empty or 0(same thing)

To check for both B8 and B9

=IF(AND(B8=0,B9=0),"",B8/B9)

To check either

=IF(OR(B8=0,B9=0),"",B8/B9)


Gord Dibben MS Excel MVP


On Thu, 18 Sep 2008 12:06:06 -0700, spezticle
wrote:

Hours 5
Tips 23
Odom1 98776
Odom2 99555
Miles odom2-odom1
Gallons 2.000
Gas $ 3.799
MPG #VALUE!

if I don't work on this day, the numbers are zero.
both gallons (B9) and miles(b8)
i guess what i need is an IF statement that is something like t his:
=IF(B8=0,0,B8/B9) OR IF(B9=0,0,B8/B9)
but that isn't correct.
i just need help with the format.
thanks :)

"spezticle" wrote:

i have a row of formulas.
b11 through h11
b8/b9, c8/c9,d8/d9, so on and so forth.
how to i prevent the formula from reporting #DIV/0! when in column B there
isn't a number, or the number is 0.

In these cases I just want it to be either blank, or a 0, not this error.
I know you can't divide by zero, i just don't know how to tell the prog that
it doesn't have to, and to just ignore

formula for spreadsheet, avoiding zeros?
 

You're Welcome!

"spezticle" wrote:

thanks much!
works seemlessly :)

"Teethless mama" wrote:

=IF(B9,B8/B9,0)


"spezticle" wrote:

Hours 5
Tips 23
Odom1 98776
Odom2 99555
Miles odom2-odom1
Gallons 2.000
Gas $ 3.799
MPG #VALUE!

if I don't work on this day, the numbers are zero.
both gallons (B9) and miles(b8)
i guess what i need is an IF statement that is something like t his:
=IF(B8=0,0,B8/B9) OR IF(B9=0,0,B8/B9)
but that isn't correct.
i just need help with the format.
thanks :)

"spezticle" wrote:

i have a row of formulas.
b11 through h11
b8/b9, c8/c9,d8/d9, so on and so forth.
how to i prevent the formula from reporting #DIV/0! when in column B there
isn't a number, or the number is 0.

In these cases I just want it to be either blank, or a 0, not this error.
I know you can't divide by zero, i just don't know how to tell the prog that
it doesn't have to, and to just ignore