0

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.

Improve this question
5
  • what type of cell formatting is applied to your cell? and if you are expecting numbers then you should test first if the cell is numeric and then check if it is whatever you want to test. for your question I don't have any technical explanation, except my experience, text fields are often considered non-zero, non-empty when there is something other than an empty value or no value ("" and null or nil values)
    – Zina
    Commented Dec 17, 2020 at 13:16
  • maybe duplicate of stackoverflow.com/questions/35050151/….
    – gns100
    Commented Dec 17, 2020 at 18:37
  • @Zina, the cell formatting is General. I've also changed the cell formatting to Text and the formula returns the same result. Your comment "... I don't have any technical explanation, except my experience, text fields are often considered non-zero ..." mirrors the experience of the Excel user's post I linked to in my post. I'm looking for an answer that explains why the Greater Than comparison operator returns a result for text when the logic behind "is this text value greater than zero" should produce an error (because you can't compare text with numbers).
    – Bill Vallance
    Commented Dec 18, 2020 at 17:33
  • in the past I was an application developer and for me it is normal to test if the values compared are of the correct type.
    – Zina
    Commented Dec 18, 2020 at 18:41
  • I am also a developer and I, too, test for variable typing in my code. It just so happens that the spreadsheet I was reviewing was created by a non-developer. So I wasn't surprised by the syntax of the statement, but I was surprised that Excel returned a TRUE result.
    – Bill Vallance
    Commented Dec 19, 2020 at 23:33

1 Answer 1

Reset to default
0

You could look into it at:

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/comparison-operators

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().

Improve this answer
2
  • Thanks for the insight Jeorge. I am very familiar with VBA. I did see the the entry in the table you referenced that said that a comparison of a number and a string would result in the number being less than the string. That comparison is performed by VBA. If the same comparison is performed by Excel, then the result I'm seeing exactly matches the comparison behavior of VBA. It's not a stretch to assume that since VBA is the programming language for Excel that the comparison behavior is the same for both "applications." Can anyone confirm this?
    – Bill Vallance
    Commented Dec 18, 2020 at 17:42
  • a wiki page says that VBA was introduced in Excel 5.0 in 1993. so if anyone has a prior version of Excel and is able to run it we could test it. a friend of mine worked in an IT company which sold HW and built PCs and sold & serviced them and they had their complete paperwork done in Excel via VBA. just a fun fact.
    – Zina
    Commented Dec 18, 2020 at 18:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .