Zero if zero yet blank if blank?
 

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?

Zero if zero yet blank if blank?
 

Try testing for the empty cell first...

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

Rick


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

Zero if zero yet blank if blank?
 

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

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

Zero if zero yet blank if blank?
 

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

On Jun 29, 3:26*pm, OBlake wrote:
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?

Zero if zero yet blank if blank?
 

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



OBlake wrote:

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?


--

Dave Peterson

Zero if zero yet blank if blank?
 

Thanks! Works perfectly!

"Niek Otten" wrote:

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

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