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.

 

[Joe4]

I cannot reproduce that situation.
The formula you posted in section 3 returns "n/a" for me, as shown below:

Are you doing this in Excel? If so, which version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[au2010]

I cannot reproduce that situation.
The formula you posted in section 3 returns "n/a" for me, as shown below:

View attachment 106520

Are you doing this in Excel? If so, which version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for the reply. I should have added more detail, but I was trying to keep the original post from being excessively long. Anyway:

1) This is Excel for Windows, version 2016, and the file has extension .xlsx
2) I originally created the file myself, and it worked as it should: a formula that tried to use text in a calculation would return an error message, as in your example
3) I sent the file to a co-worker to add some data, and when it came back, it no longer worked: a formula that tried to use text in a calculation would return the value 0 instead of an error message
4) The co-worker says he does not know why this happened and that he did not change any settings
5) If I insert a new tab into the workbook, it's fine: formulas that try to use text in a calculation return an error value, as they should
6) But in the tabs that I had originally created, this no longer works: formulas using text in a calculation return 0 instead of error message

Is there some setting or option in Excel that covers how Excel treats text when it encounters them in a formula?

Thank you.

 

[Joe4]

I have not heard of anything like that happening before.
Are we sure that the formulas are still in that cell, and have not been overwritten?

What happens if you go to one of the offending sheets, and press the F9 key?
Does anything change?

 

[au2010]

I have not heard of anything like that happening before.
Are we sure that the formulas are still in that cell, and have not been overwritten?

What happens if you go to one of the offending sheets, and press the F9 key?
Does anything change?

1) Yes, the original formulas that I created are still there, just as I wrote them, but they are not working (as described above)
2) F9 does not change anything. I also tried Ctrl+Alt+F9, but that had no effect either
3) I also tried saving the file to a new location and changing the extension to .xlsb but that also did not help
4) Then I closed the file, quit Excel, reopened the file and tried F9 again - still not working
3) I tried adding a new test formula to the offending sheet: that also does not work (Text in a calculation returns 0 instead of Error)
4) BUT - if I insert a new sheet and try a test formula, it DOES work: using text in a calculation returns an error message

I'm at a loss. Any other suggestions?

 

[Joe4]

Can you provide an example of one that is not working properly?
What are the exact values in all the cells that the formula is referencing?

 

[Fluff]

Goto File, options, advanced, scroll to the bottom & make sure that "Transition formula Evaluation" is unchecked.

 

[au2010]

Thank you! That fixed it. "Transition Formula Evaluation" was checked. Unchecking it solved the problem.

Thanks, both, for your replies.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

Interesting... I have not come across that before.

@Fluff, do you know what would cause that to be checked?
Would it be some settings on the other user's computer?