show 0.00 if referenced cell is empty

D

David D

this formula =2240/(B9*7.2) will show an error until something is entered
into cell B9

I would like it to be 0.00 until B9 is populated, how is this done?

Thanks, David
 
M

Mark Lincoln

this formula =2240/(B9*7.2) will show an error until something is entered
into cell B9

I would like it to be 0.00 until B9 is populated, how is this done?

Thanks, David
Click to expand...

=IF(ISNUMBER(B9),2240/(B9*7.2),0)

Mark Lincoln
 
V

Vasant Nanavati

=IF(B9="",0,2240/(B9*7.2))
_______________________________________________________________________
 
D

David D

That works great until I try to use it in a cell that adds 2 cell together;

Cell B9= empty or blank cell
Cell B10= no error msg. where I have the formula,
=IF(ISNUMBER(B9),2240/(B9*7.2),0)


Cell B22= =SUM(B9,B16)
Cell B23= #DIV/0! where I have the formula,
=IF(ISNUMBER(B9),2240/(B9*7.2),0)
 
M

Mark Lincoln

Cell B23 in your second example should produce exactly the same result
as cell B10 in the first example; it's the same formula. Should it
have referred to cell B22?

Regardless, I didn't test for zero in my original reply. Here's an
amended formula:

=IF(AND(ISNUMBER(B9),B9<>0),2240/(B9*7.2),0)

This sets the result to zero unless cell B9 is both numeric and
nonzero. Sorry for the trouble.

Mark Lincoln
 
D

David D

Mark, Thanks that worked fine...

David

Mark Lincoln said:
Cell B23 in your second example should produce exactly the same result
as cell B10 in the first example; it's the same formula. Should it
have referred to cell B22?

Regardless, I didn't test for zero in my original reply. Here's an
amended formula:

=IF(AND(ISNUMBER(B9),B9<>0),2240/(B9*7.2),0)

This sets the result to zero unless cell B9 is both numeric and
nonzero. Sorry for the trouble.

Mark Lincoln
Click to expand...
 
T

Teethless mama

shorter version

=IF(B9,2240/(B9*7.2),0)


Mark Lincoln said:
Cell B23 in your second example should produce exactly the same result
as cell B10 in the first example; it's the same formula. Should it
have referred to cell B22?

Regardless, I didn't test for zero in my original reply. Here's an
amended formula:

=IF(AND(ISNUMBER(B9),B9<>0),2240/(B9*7.2),0)

This sets the result to zero unless cell B9 is both numeric and
nonzero. Sorry for the trouble.

Mark Lincoln
Click to expand...
 
M

Mark Lincoln

That works as long as B9 is blank or a number, but produces a #VALUE!
error if B9 is nonnumeric, i.e., text.

I would rather most of my users see a zero result as opposed to the
error message--they don't panic nearly as badly. ;)

Mark Lincoln
 
Top