I was troubleshooting what I thought was a basic IF formula that I expected to produce a FALSE result. The formula is:
=IF(A1>0,"True value","False value")The formula is placed in cell C1. When the value of cell A1 is a text value, the result of the IF function is "True value" (without the quotes). I was expecting the result to be "False value" because the value in cell A1 is text. This happens when using obvious text, like a value that contains only letters (like "yellow")!
What is going on? I read this post from a user who conducted his own experiments about this problem. I accept his explanation at face value, but can somebody PLEASE provide a more technical explanation as to why text is considered greater than zero in Excel? My expectation was that Excel would error out when using a greater than comparison operator on text.
51 Answer
You could look into it at:
which is concerned with how things happen in VBA, but given that VBA can directly draw upon the cell values without declaring a variable and therefore a type, it presumably is able to decide type from the raw material and move forward.
About 2/3 down the page, one of the tables (not the first that seems to address the subject) has the most "here-applicable" instances of what combinations do.
Essentially, the idea is that if one of the values is a string, the comparison performed will be a string comparison which seems to invoke whatever exact process is used for a sort on cells. So as one would expect when sorting, numeric values will come before string values. Others fit in as well: logical values, nulls, blanks, etc. You will see there are rules VBA uses (and therefore likely cell-side Excel too) so, for instance, a blank will likely be treated as a 0 but a null likely will not depending upon the exact type mismatched.
To avoid the problem, check for text first in another IF() handling its existence as one needs to do, then perform the ">" comparison. Use ISTEXT() for instance, or even just:
=IFERROR(IF(A1,"True value","False value"),"Non-numerical value present")It would work for your exact use because you are testing for a number not requalling 0. In the above IF() test, Excel looks at A1 and performs a logical test only: a 0 is FALSE and any other numerical value at all is TRUE which is the same as your A1>0 test. So if it is other than 0, you get "True value" and if 0, you get "False value"... but over it all, so to speak, the logical test produces a #VALUE! error if bith values are not numbers so you separate all the strings out with the IFERROR().
