xlf QandA series
Floating point precision, and cell formats
QUESTION
WHY DOES THE LOGICAL EXPRESSION FORMULA =0.1=(1.2-1.1) RETURN FALSE?
Answer
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 than 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 floating point numbers.
2. Using Evaluate Formula to display the floating point precision value
3. Using F2 and F9 to display the floating point precision value
4. Floating point examples
- Download the Excel file for this module: click on the DownLoad button at the bottom of figure 4
- Development platform: Excel 2016 Pro 64 bit.
- Published: 19th May 2017
- Revised: Saturday 25th of February 2023 - 09:39 AM, [Australian Eastern Standard Time (EST)]