How does the rounding of the number with a floating point work in the Excel?

Question

I encountered some unexpected behavior in Excel—writing numbers to a file and their representation in the editor differs from what I expected. For example, the result of the multiplication =0.6 * 3 will be 1.8 in the cell, but 1.7999999999999998 in the file, which also matches the result of the calculation in Python. However, if I use the calculation inside the TRUNC(0.6 * 3, 15) formula, the result will be 1.8 instead of the expected 1.7999999999999998. Additionally, the comparison clean(1.7999999999999998) != clean(0.6 * 3) holds true. Is this a bug, or does Excel round values internally under the hood? I'm also curious about how many decimal places Excel uses for calculations, because, for example, the comparison =1.7999999999999991 = 1.7999999999999999 returns TRUE and rounds the values in the cell to =1.79999999999999 = 1.79999999999999.

Answer 1

What you see in the file depends on how you write the data. Excel stores numbers in binary. 0.6 cannot be represented exactly. Your product is the result of approximating 0.6 and multiplying by 3, an approximation of 1.8.

What you see in the cell is always the value converted to decimal and rounded according to Excel's rules and limits.

If you write the raw binary data to the file, what you see in the editor is the value converted to decimal and rounded according to the editor's rules and limits.

Row 1 of you spreadsheet gives a big clue since the two decimal values are not equal yet the binary values compare equal. On a 64-bit system, a floating point value is accurate to at most 16 decimal digits.