Is FALSE Excel infinite?

Question

Why is =FALSE<10000000000 evaluated as FALSE and =FALSE>10000000000 as TRUE ? I tried several different numbers, and that seems to be always the case.

Answer 1

This is by design. Search "Sort troubleshooting" to see the default sort order.

In ascending order, Microsoft Excel uses the following order.

Numbers: numbers are sorted from the smallest negative number to the largest positive number.

Alphanumeric sorting: When sorting alphanumeric text, Excel sorts from left to right, character by character. For example, if a cell contains the text “A100”, Excel places the cell after the cell containing the record “A1” and before the cell containing the record “A11”.

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space)! "# $% and () *,. /:;? @ [\] ^ _` {|} ~ + <=> ABCDEFGH I JKLMNOPQRSTUVWXYZ

Apoptops (') and hyphens (-) are ignored, with one exception: if two text lines are the same, except for a hyphen, the text with a hyphen is sorted last.

Logical values: in logical values, FALSE is placed before TRUE.

Error Values: All error values ​​are equal.

Forms: spaces are always placed last.

The default sorting value matters because that's how Excel was designed to compare different types of data. Boolean values ​​are always after text and numbers. Error values ​​are always after that. Forms always remain last. When you use comparison operators (<, <=, =, etc.), It uses the same comparison algorithm as sorting (or, more likely, sorting alogrithm uses the code of the comparison operator, which makes them identical).

TRUE<>1 according to the sort order, but --TRUE=1 . The formula analyzer acknowledged that you are trying to deny something. If it is a boolean, it converts it to 0 or 1. There is nothing 0-ish or 1-ish relative to the boolean, it is simply the result of the Type Coercion internal function. If you type --"SomeString" , it will do the same. It sends a string to the "Type of enforcement" function, which says "Unable to force" and ends with #VALUE! in the cell.

This is the answer "Why is this so." I don’t know why they designed it like that.

Answer 2

Obviously, logical TRUE / FALSE are different data types for numbers. Check this out ( http://msdn.microsoft.com/en-us/library/office/bb687869.aspx ) to see that the boolean variables are stored in 2 bytes (or something like a short integer for a specific architecture). However, this is the memory in which the data is stored, because excel really has a special data class for Boolean vars. In particular: xltypeNum for numbers, xltypeStr for strings, and xltypeBool for discussion.

The relationships between the same types are clear, now what does TRUE <1000 do ?? probably nothing meaningful-useful.

Ways to solve this problem:

 =ABS(BOOLEAN_VAR), ie =ABS(FALSE) --> 0 and =ABS(TRUE) --> 1 

or

 =INT(BOOLEAN_VAR), ie =INT(FALSE) --> 0 and =INT(TRUE) --> 1 

or

 =BOOLEAN_VAR*1, ie =FALSE*1 --> 0 and =TRUE*1 --> 1 

or

 =+BOOLEAN_VAR, ie =+FALSE --> 0 and =+TRUE --> 1 

As you can see, you force the force to output the numeric data type, either by providing a boolean value to the function, or by using the boolean expression var in the expression.