New Excel Forum

This forum has been moved to

Ask all future questions in the New Excel Forum.


Why does Excel pretend that "1" is larger than 2

Free Excel Tips and Tutorials

Why does Excel pretend that "1" is larger than 2

Postby pecoflyer » Mon Feb 08, 2016 10:06 am

We all know that if A1 contains the number 1 and A2 number 2, entering =A2<A1 in A3 returns FALSE.

OTH if we enter "1" in A1 ( text string) A3 returns TRUE.

Why is that ?

When comparing cell values, XL first compares the outcome of the TYPE() function between both cells.
(I'll leave the gentle reader to check that function for himself),and then compares values if the outcome is identical.

In our second case, =TYPE("1") thus returns 2 and =TYPE(2) returns 1.

XL thus say : " Yes, "1" is larger than 2".

The odd thing is, to a user that has, say, formatted cell contents to center, cell A1 contains 1 and A2 contains 2 , but A3 states that 1 is larger than 2, which is somewhat astonishing.

My advice : do not format cells too rapidly, because, as you all know, Excel aligns text left and numbers right, which
will help you find your " mistake" easily.
A relevant topic title helps get faster and more answers
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 25 guests