Calculation error when using VLOOKUP due to rounding

Originally Posted by Baconfish

When I do this calculation: FLOOR.MATH(564,3-FLOOR.MATH(564,3;1);0,1) I would expect the formula to return 0,3, but it actually returns 0,2.
[....]
If instead I'm using the approximated match with the VLOOKUP, then the formula would only need to look like this:
564,3-FLOOR.MATH(564,3;1)
then the match would take care of the ounding, but I am unsure whether or not the error occurs here to be honest.
Does anyone have an idea as to how I can get around this annoying, erratic error?

Note: I use period for the decimal point, where you use comma; and I use comma to separate parameters, where you use semicolon. Moreover, my version of Excel only has FLOOR; but I believe that is the same as FLOOR.MATH as you use it.

Yes, that is probably at least one source of your problems. Honestly, I am not bothering to look at anything else.

You seem to want to "extract" the fractional part of a value, rounded to 1 decimal place. Am I correct?

If so, a simpler formula is:

=ROUND(MOD(564.3,1),1)

First, note that 564,3-FLOOR.MATH(564,3;1) only appears to be 0.3. If that formula is in A1, note that =A1=0.3 return FALSE(!).

In any case, even it worked, it would be by coincidence. In other words, it might not work for other values.

There are two issues....

First, most decimal fractions can only be approximated in 64-binary floating-point, which Excel uses to store numeric values. Moreover, the approximation depends on the magnitude of the integer part. For example, the exact value is on the right:

Please Login or Register  to view this content.

Here, I use period for the decimal point, and comma to demarcate the first 15 significant digits, which is all that Excel displays and formats, an arbitrary limitation.

Note the difference in the approximations of 0.3 (3/10).

So in general, when we expect a calculation to be accurate to n decimal places, we should explicitly round to that number of decimal places. (Not to an arbitrary number of decimal places like 10, as some people suggest.)

Second, because of these approximations, FLOOR(.MATH) is unreliable when the second parameter is a decimal fraction (unless it is an exact power of 2).

Actually I want to dissect the number 564,3 to become 500, 60, 4 and 0,3.

I've reworked the thing completely as it was much too complex, and it is now easier to work with. I also got rid of the error that kept bugging me by using the VLOOKUP entirely instead of the MATCH and INDEX.

The solution is shorter and much more elegant. As you can see, I'm adding 0,01 to the decimal number and then letting the VLOOKUP round the number by selecting an approximate match rather than a exact. For the rest, an exact match is still needed.

Thank you for the indepth explanation of decimals in excel, that'll be sure to help me later on.

Edit#1 Apparently I'm notallowed to add the formula to this reply, so I'll add it to the post instead if that's possible.

Edit#2 I'm not allowed to do that either, so I can't really show my working code

Originally Posted by joeu2004

You seem to want to "extract" the fractional part of a value, rounded to 1 decimal place. Am I correct?

Obviously not! Sorry about that.

If the value were 564.29, presumably you want 0.2. My ROUND formula returns 0.3. Klunk!

Ostensibly, the correct formula is:

ROUNDDOWN(MOD(564.3,1),1)

But that still returns 0.2 (sigh).

Again, the problem is with the limited approximation of 0.3 due to the magnitude of the integer part (564). Consequently, the exact decimal represetation of 564.3 is 564.299999999999,954525264911353588104248046875. So MOD(564.3,1) returns exactly 0.299999999999954,525264911353588104248046875, which of course should round down to 0.2.

Moreover, I assume that 564.3 is really a calculated value. So what appears to be 564.3, due to formatting, might be something else, even if it appears to be 564.300000000000 when formatted to show 15 significant digits.

Previously, I said that if you expect calculations to be accurate to n decimal places, you should explicitly round to that number of decimal places. So if you expect calculations to be accurate to 5 decimal places, you should write:

ROUNDDOWN(ROUND(MOD(564.3,1),5),1)

However, for some calculations, we have no accuracy expectations; and imposing one might alter the outcome adversely. Example: financial interest calculations (amortization).

Off-hand, I cannot think of any general solution to the numerical error caused by the "truncation" of the approximation of 0.3 in 564.3.

Maybe there is one, and it just escapes me at the moment. (I'm have a "senior moment".)

Many people suggest weird adjustments like adding/subtracting 1E-10 or rounding to 10 decimal places or some-such. Some people might suggest making that adjustment in the VLOOKUP/MATCH look-up range. None of that works in general.

I'll continue to think about it. But I don't have much hope. It would be nice to know if the "round to n decimal places" solution is sufficient for your purposes.