Forum Discussion

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Jan 28, 2017

Issues in Comparison Formulas

Do any of you can explain the following issues?

 

="text">1   (or any other number)

Result: TRUE

 

="text"<1  (or any other number)

Result: FALSE

 

=#N/A=1

In this case, the formula will return the same error, and it's doing this with all other errors.

 

 

Formulas & Functions
Requests

2 Replies

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Jan 29, 2017

    Any error that is not trapped in a formula, for example with an IfError() function or similar, will pass on the error to the formula result. That is expected behaviour, since the idea is that you'd want to troubleshoot and remove errors, or at least be alerted to errors.

     

    Re comparing text to numbers: Excel sorts text after numbers, so any text will always be regarded greater than any number. You can test that. Enter a mix of text and numbers into a range and then sort the range.

     

     

    cheers, teylyn

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 28, 2017

    My guess it compares binary values, binary value of the text is always more than binary value of the number. For example =("1">1) returns TRUE since that's using binary value =(0011 0001 > 0000 0001). If i remember correctly what the binaries are...

     

    And if you have any error in intermediate calculations of the formula final result gives that error code

Resources