Incorrect calculation

New Contributor

Cell A1 1.16.11

Cell B1 1.16.9

Cell C1 =B1<A1

Result is false but should be true, 1.16.9 is less than 1.16.11

2 Replies
best response confirmed by Sergei Baklan (MVP)


Excel treats these values as text, so it sorts them alphanumerically, not as numbers. 11 comes before 9 since 1 comes before 9.

One workaround is to use 1.16.09 instead of 1.16.9.

Another is to use Data > Text to Columns, with point (dot, period) as delimiter. and sort on the resulting columns.

@Hans Vogelaar Thank you for the explanation and suggestions.

The first suggestion addresses my issue.