Zero if zero yet blank if blank?

[OBlake]

I am trying to evaluate a cell and return zero if zero is put into the cell,
blank if the cell is left blank and evaluate a formula if a number other then
zero is entered.
I have tried:
=IF(AJ7=0,0,(Formula) with and without conditional formating 0;-0;;@
as well as
=IF(AJ10=0,0,IF(AJ10=",",",",(Formula)))
as well as
=IF(AJ10=0,0,IF(AJ10="","",(Formula)))
It seems that a blank cell and a cell with a '0' in it are treated the same??
Does anyone have anymore ideas?

 

[Rick Rothstein \(MVP - VB\)]

Try testing for the empty cell first...

=IF(A7="","",IF(A7=0,0,<<your formula>>))

Rick

 

[Niek Otten]

Just change the order in which you test:

=IF(A1="","",IF(A1=0,0,A1))

Blank is treated as zero (in this case), but not the other way around!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am trying to evaluate a cell and return zero if zero is put into the cell,
| blank if the cell is left blank and evaluate a formula if a number other then
| zero is entered.
| I have tried:
| =IF(AJ7=0,0,(Formula) with and without conditional formating 0;-0;;@
| as well as
| =IF(AJ10=0,0,IF(AJ10=",",",",(Formula)))
| as well as
| =IF(AJ10=0,0,IF(AJ10="","",(Formula)))
| It seems that a blank cell and a cell with a '0' in it are treated the same??
| Does anyone have anymore ideas?
|

 

[Pete_UK]

If the cell is EMPTY, then you can use this to distinguish:

=IF(ISBLANK(AJ7),"",IF(AJ7=0,0,your_formula))

Hope this helps.

Pete

 

[Dave Peterson]

=IF(A1="","",IF(A1=0,0,<formula>))

I am trying to evaluate a cell and return zero if zero is put into the cell,
blank if the cell is left blank and evaluate a formula if a number other then
zero is entered.
I have tried:
=IF(AJ7=0,0,(Formula) with and without conditional formating 0;-0;;@
as well as
=IF(AJ10=0,0,IF(AJ10=",",",",(Formula)))
as well as
=IF(AJ10=0,0,IF(AJ10="","",(Formula)))
It seems that a blank cell and a cell with a '0' in it are treated the same??
Does anyone have anymore ideas?

 

[OBlake]

Thanks! Works perfectly!