reading a blank cell as zero

[Dana Stricker]

I'm a first time poster so please be patient if I don't give enough detail.
I'm trying to put an if stmt in one cell and conditionally format that cell.
My current formula is =IF(D10>=0,D10,NA()). But when D10 is blank it puts a
zero in E10 and thus my conditional format colors the cell. What I want is
#NA to appear in E10 which blocks the conditional formatting. This seems easy
enough but won’t work. I've tried different formulas with no luck.
Thanks for any help.
Dana

 

[JR Hester]

If you want the text "#NA" to appear in cell E10, try this modification to
your formula =IF(D10>=0,D10,"#NA").

hth

 

[Tom Hutchins]

Try

=IF(AND(LEN(D10)>0,D10>=0),D10,NA())

Hope this helps,

Hutch

 

[Dana Stricker]

Hutch - you're my new hero. Works perfect. However, new problem. I have an
autosum of column E which is now giving me #NA unless all cells are filled.
Do you know of another formula that reads only cells with a value of zero or
greater?
I'm trying =IF(E6:E22>0,SUM(E6:E22)) but getting #VALUE!

 

[Tom Hutchins]

Your other formula may cause some cells in column E to contain #N/A. That
will interfere with any SUM function unless we work around it. The other
thing is, to test a range of cells with an IF function you have to enter it
as an array formula. I think the following array formula will work:

=SUM(IF(ISERROR(E6:E22),0,E6:E22))

This is an array formula. Hit Ctrl-Shift-Enter instead of Enter. If you do
it
correctly, Excel will wrap curly brackets { } around your formula (don't add
them yourself.) You can copy/move/paste array formulas like regular
formulas, but if you edit it, you always have to press Ctrl-Shift-Enter
instead of Enter.

Hutch

 

[Dana Stricker]

Hutch - once again it works perfectly!
thank you so much for your time and help.
Dana