Excel FLOOR() function

Question

The FLOOR() function is said to do no rounding if the value is already a multiple. Why does the FLOOR() function round down a full multiple when the value is calculated from other cells? For example: A1 =(0.4*60); A2 =FLOOR(A1, 1) = 24. BUT if A1=0.4, A2=60, A3 = A1*A2, FLOOR(A3, 1) = 23. This happens with cell format as General and as Number. Could anyone please explain why the results are different?

Answer 1

floating point calculations in Excel can sometimes produce incorrect results.

I would say "unexpected" results, not "incorrect". In fact, they are precisely correct. 😉

The point is: most decimal fractions cannot be represented exactly in binary "floating-point", which is what Excel uses internally to represent numeric values and perform arithmetic.

And the binary approximation of some decimal fractions might vary depending on the magnitude of the number. That is why, for example, 10.01 - 10 = 0.01 returns FALSE(!). (Hint: Format the result of 10.01 - 10 to display 17 decimal places.)

Answer 2

Tony wrote:

if A1=0.4, A2=60, A3 = A1*A2, FLOOR(A3, 1) = 23

Not true, as HansV demonstrated.

Format A1 and A2 as Number with 15 decimal places (for this example). You might see that A1 and A2 are not as they appear.

However, one or both might be infinitesimally less than they appear, and we cannot see it because Excel formats only up to 15 significant digits, rounded.

To discover that, enter formulas of the following form into B1 and B2 (B1 for example):

=SUM(A1, -(A1 & ""))

formatted as Scientific. You might see values of the form -1.23E-16 (read: negative 1.23 times 10 to the -16 power).

The point is: the value in A1 and/or A2 was calculated, then perhaps copied and pasted-value.

To avoid the infinitesimal differences, explicitly round the calculations to the precision that you require, perhaps 1 decimal place.

---

PS.... =SUM(A1, -(A1 & "")) shows the difference between the actual binary value and the displayed value, subject to Excel's limitations. We cannot enter simply =A1-(A1 & "") or =A1<--(A1 & "") because of tricks that Excel plays to try to hide such infinitesimal differences.

Answer 3

I know, Joe.

The explanation was primarily for Tony's benefit (and other readers). You did write Joeu2004 will explain (I hope). So, I explained.

Answer 4

I know, Joe.

Answer 5

This is what I get:

But floating point calculations in Excel can sometimes produce incorrect results. Joeu2004 will explain (I hope).