xlf QandA series
Floating point precision, and cell formats
WHY DOES THE LOGICAL EXPRESSION FORMULA =0.1=(1.2-1.1) RETURN FALSE?
1. Excel numbers and cell formats
- Excel numbers are stored and processed as Double Precision floating point numbers (64 bit)
- The numbers displayed in the cell have a number format. General by default, except for Dates, Times, and Percentage
- The topic of Floating Point numbers is outside the scope of 90045
The short answer to the question is: because of the way floating point numbers are processed by a computer, an expression like 1.2-1.1 actually returns 0.0999999999999999 - 15 digits (ignoring the leading zeros and decimal indicator), rather the expected value of 0.1. This does not happen in every instance, as illustrated by the examples in figure 4.
Several methods are displayed to illustrate this feature of Double Precision numbers.
2. Using Evaluate Formula to display the double precision value
3. Using F2 and F9 to display the double precision value
4. Floating point examples
- This example was developed in Excel 2016 Pro 64 bit.
- Revised: Thursday 3rd of May 2018 - 08:17 AM, [Australian Eastern Standard Time (EST)]