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 wont work. I've tried different formulas with no luck.
Thanks for any help.
Dana

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

hth

"Dana Stricker" wrote:

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 wont work. I've tried different formulas with no luck.
Thanks for any help.
Dana

Try

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

Hope this helps,

Hutch

"Dana Stricker" wrote:

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 wont work. I've tried different formulas with no luck.
Thanks for any help.
Dana

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:E220,SUM(E6:E22)) but getting #VALUE!

"Tom Hutchins" wrote:

Try

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

Hope this helps,

Hutch

"Dana Stricker" wrote:

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 wont work. I've tried different formulas with no luck.
Thanks for any help.
Dana

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" wrote:

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:E220,SUM(E6:E22)) but getting #VALUE!

"Tom Hutchins" wrote:

Try

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

Hope this helps,

Hutch

"Dana Stricker" wrote:

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 wont work. I've tried different formulas with no luck.
Thanks for any help.
Dana

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

"Dana Stricker" wrote:

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 wont work. I've tried different formulas with no luck.
Thanks for any help.
Dana