Excel formula does not return expected result when encountering a cell that includes text in a formula reference

[au2010]

Hello. I have an Excel file that for some reason seems to treat cells with TEXT as if they had the value 0, and I can't figure out how to fix it.

1) For example, my file has a series of values in Column B that either be numeric or text (some rows are numbers, some rows are text).

2) In Column D, I have a formula to divide Column B by the value stored in Cell $D$1 IF Column B is a number, or else return "n/a"

3) I did this with the formula =iferror(B5/$D$1,"n/a")

4) It works fine as long as row B contains a numeric value. But it doesn't work if row B contains TEXT: in that case, the value returned in Column C is 0 (numeric) instead of "n/a" (text). In other words, Excel seems to be treating the text in Column B as the number zero, so that B5/$D$1 = 0/$D$1 = 0, instead of evaluating TEXT/D1 as an error and returning "n/a."

5) It does work if I change the formula to =if(istext(B5),"n/a",B5/$D$1), but it would be cumbersome to change all necessary formulas like that.

6) As a test, I put the text "TEST" into cell B1 and then in C1 I put the formula =B1/1. I was expecting #VALUE but I got 0, as if Excel interpreted the word TEST as the value zero in the formula.

Any idea what would cause this or how to fix it?

Thank you.

 

[Fluff]

Not sure why, or how, it was checked as I think the default is unchecked (but could be wrong). But I've seen people have similar problems & the old Lotus compatibility settings were to blame.

 

[Joe4]

Yeah, I was wondering if maybe they tried viewing it in something like Lotus or an old version of Excel, and that is what caused it to flip.