Formatting Cell that Contains If/Then Formula

[Janna]

I have a cell with the following formula:

=IF(A1=0," ",IF(E17>0,+E17,"0"))

I'd like to format the cell with a Currency symbol, but can't seem to figure
out how to do so. As long as the above formula is in the cell, the number
displays without the currency symbol, even though I've formatted it as
currency by going to format cells, number, currency.

If I remove the above formula and just type a simple formula like =A1*A2
the number is formatted correctly with the currency symbol.

What am I doing wrong? Thanks

 

[Tevuna]

Does this work for you?

=IF(A1=0," ",IF(E17>0,TEXT(E17,"$#,##0.00"),"0"))

 

[Dave Peterson]

What's in E17? Are you sure it's numeric?

If you put this formula in an empty cell:
=isnumber(e17)
what is returned?

If that formula returns False, then E17 is not a number. That means that the
number formatting won't do anything. I'd change the value in E17 to a real
number (format E17 as general and retype the entry).

I'd also use this formula:
=IF(A1=0,"",IF(E17>0,+E17,0))

I don't like cells with " " (a space character) or a text 0 ("0") in them.

 

[Fred Smith]

If E17 is greater than 0, you should get e17 formatted as currency.

However, if e17 is not greater than zero, you get zero displayed as text,
because you put it in quotes. Is this the problem you see? You get 0, rather
than $0.00?

If so, remove the quotes from the last zero. In addition, the plus sign before
e17 is superfluous. Your formula can be simplified as:

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

which can be further simplified to:

=if(a1=0," ",max(e17,0))

 

[Tevuna]

BTW what good does the plus sign before the E17 Reference do? If the number
is negative it's by definition less than zero?

 

[Dave Peterson]

It doesn't do anything. I'd remove that, too.

BTW what good does the plus sign before the E17 Reference do? If the number
is negative it's by definition less than zero?