The term “BigNum”, which stands for “Big Number”, is used to represent the largest allowed positive number in Excel, which is 9.99999999999999E+307
BigNum is used in certain lookup formulas constructed in a way to find the largest value that is less than or equal to a search value. Because BigNum is an improbably large value, the lookup will find the previous numeric value.
For example, you can use the following formula to find the position of the last numeric value in a column:
=MATCH(9.99999999999999E+307,range)
In practice, any improbably large value will do, so you could trim the above to:
=MATCH(9.99E+307,range)
The reason this works has to do with the default behavior of MATCH, which uses a binary search algorithm to find the largest value that is less than or equal to the search value in a range. In this (default) mode, MATCH assumes values are sorted in descending order. When BigNum is, by design, not found, MATCH returns the position of the last number in the range.
For more details, see: Last row in numeric data.
BigNum with other lookup functions
The BigNum approach can be used with other functions that support binary search, for example:
=MATCH(bignum,range,1)
=VLOOKUP(bignum,range,1,1)
=HLOOKUP(bignum,range,1,1)
=LOOKUP(bignum,range)
Note match type is set to 1 in the first three examples to explicitly set binary search, often referred to as approximate match.
Boolean algebra is a mathematical system that represents logical expressions and relationships using only two values: TRUE and FALSE. Boolean logic refers to the principles that support Boolean algebra, including logical operations like AND, OR, and NOT and rules that govern the manipulation of logical expressions in Boolean algebra.
In the context of Excel, Boolean logic refers to a technique of building formulas to take advantage of the fact that TRUE can be represented by the number 1, and FALSE can be represented by the number 0. In fact any math operation in Excel will coerce TRUE and FALSE values into 1’s and 0’s automatically, as seen in the simple examples below:
=TRUE+0=1
=TRUE*1=1
=FALSE+0=0
=FALSE*1=0
Boolean logic can be used to simply formulas and eliminate the branching seen in nested IF formulas. In the example shown, the formula in cell E5 checks that group is “red” and score is > 80 like this:
=(C5>80)*(D5="red")
=(TRUE)*(TRUE)
=1
This is functionally equivalent to the formula below based on the IF function and the AND function :
=IF(AND(C5>80,D5="red"),1,0)
Boolean logic in array operations
Boolean logic often shows up inside other formulas and functions to perform array operations . The SUMPRODUCT function is a good example. To count cells in A1:A10 that are equal to “red” with SUMPRODUCT, you can use a formula like this:
=SUMPRODUCT(--(A1:A10="red"))
The double negative coerces the TRUE and FALSE values that result from the expression A1:A10=“red” to 1s and 0s, and SUMPRODUCT then returns the sum of these numbers, which corresponds to the count of cells that contain “red”. To sum numbers in B1:B10 when the value in A1:A10 is “red”, you can use a formula like this:
=SUMPRODUCT(--(A1:A10="red")*B1:B10)
SUMPRODUCT has special features that make it especially suitable for this kind of formula in older versions of Excel. In the latest version of Excel, you can also use the SUM function like this:
=SUM(--(A1:A10="red")*B1:B10)
For another practical example of Boolean logic, see INDEX and MATCH with multiple criteria .
