Returning number as text in IF formula

**Canucklehead** · 10-06-2009, 10:56 AM

Hello,

This is probably a pretty basic question, but I can't find an answer in Excel help or on the interwebs. I have an IF formula and I'd like 0 returned if the statement is false (e.g. =IF(A1<>0, A1, 0)); however, I would like the 0 returned as text. I've tried the TEXT function and using ` in different places, but all to no avail. Any suggestions would be appreciated.

Thanks!

**NBVC** · 10-06-2009, 10:59 AM

You mean?

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

**Canucklehead** · 10-06-2009, 11:01 AM

I've tried that, but I can still manipulate the resulting output (e.g. I tried it using "5" and the 5 would still be counted for =AVERAGE purposes).

**NBVC** · 10-06-2009, 11:03 AM

Perhaps you can attach a small spreadsheet showing exactly what your ultimate goal is? This may help speed things up.. and limit guessing.

**Canucklehead** · 10-06-2009, 11:12 AM

Okay, here it is. I note now that using a function (e.g. =AVERAGE) ignores the text-number (in this case I used 5 as a sample); however, a straight-up addition includes the 5, even though it is stored as text. I suppose I can avoid the issue altogether if I just use =SUM.

**NBVC** · 10-06-2009, 11:15 AM

Well, I guess then you solved it... use SUM() and AVERAGE() ignores text...

use +,*,-,/ does not ignore text.. they convert to numbers...

**DonkeyOte** · 10-06-2009, 11:17 AM

I suppose I can avoid the issue altogether if I just use =SUM.

Echoing NBVC the use of mathematical operators will automatically attempt to coerce values to numerics - returning #VALUE! when the coercion is not valid, eg

=0+"4" -> 4
=0+"apple" -> #VALUE! as "apple" can not be coerced to number

As already outlined the likes of SUM & AVERAGE will not explicitly coerce and will only look to numeric values.

=SUM(0,"4") -> 0