ROUNDUP not working

The problem is an anomaly with binary arithmetic and the internal implementations of ROUND, ROUNDDOWN and ROUNDUP.

ROUND(-0.25,1) returns the exact binary representation of -0.3. In other words, MATCH(ROUND(-0.25,1),{-0.3},0) returns 1.

But ROUNDUP(-0.21,1) does not. In other words, MATCH(ROUNDUP(-0.21,1),{-0.3},0) returns #N/A, indicating no match.

(But ROUNDUP(-0.21,1)=-0.3 returns TRUE because of a trick that Excel plays. [1])

Work-around: MATCH(ROUND(ROUNDUP(-0.21,1),1),{-0.3},0)

I suspect ROUNDDOWN also does not always return the exact binary representation, although it seems to work for some values that result in 0.2 and 0.3.

I think it would be prudent to use ROUND(ROUNDDOWN($D26,1),1) as well.

(I'm in a hurry. I might post more details later.)

PS.... Changing from VLOOKUP(...,FALSE) to VLOOKUP(...,TRUE) might seem to work because VLOOKUP does not look for an exact match. However, whether or not it always matches the correct row is coincidental, since the internal binary result of ROUNDUP (and ROUNDDOWN) might be infinitesimally less than expected in some cases.


-----
[1] Not a trick, so much as a limitation. For comparison operators ("=", "<>", etc) and similar operations (SUMIF, COUNTIF, etc), Excel compares the left and right operands rounded to 15 significant decimal digits. But for lookup comparisons (MATCH, VLOOKUP, etc), Excel compares the internal binary representations.

can you attach a small file with your data?
I just tried =Roundup(xx,1) function with values in the -.21 thru -.29 range and it works fine.

First, I believe the following simplification does what you want without encountering any anomalies:

VLOOKUP(INT($D26*10)/10, Data!$A$4:$P$65, 3, FALSE)

If you want to exercise an abundance of caution, you could write ROUND(INT($D26*10)/10,1). But I believe the explicit rounding is superfluous in this particular case.

-----

To explain further....

Excel uses the industry-standard 64-bit binary floating-point to represent numeric values internally. Consequently, most decimal fractions cannot be represented exactly. And the binary representation of the same decimal fraction varies depending on the magnitude of the integer part of the value.

That gives rise to such anomalies as IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!). These anomalies are not considered to be defects. Instead, they are accepted consequences of choosing that form of representation internally. (FYI, choosing any other form of representation is no panacea, in general.)

To further complicate things, Excel applies some heuristics of its own in order to mask some anomalies in very specific cases. However, the heuristics are applied inconsistently. Consequently, we can have cases where A1=A2 is TRUE, but A1-A2=0 is FALSE, and cases where A1=A2 is FALSE, but (only the formula) =A1-A2 returns exact zero (0.00E+00 when formatted as Scientific). Those anomalies are unique to Excel.

In general, the work-around is: whenever we expect a calculation to be accurate to "n" decimal places, explicitly round to that number of decimal places using ROUND. (Do not round to an arbitrary number of decimal places, as some people suggest.)

For your example (it does not matter whether we use positive or negative numbers)....

Please Login or Register  to view this content.

I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel will format (rounded).

Note the difference between ROUNDUP(0.23,1) and 0.3 and between ROUNDUP(2.23,1) and 2.3. In contrast, note that no such difference arises between ROUNDUP(1.23,1) and 1.3. So whether or not a difference arises is difficult to predict.

Based on many years of experiments, I am very confident that ROUND "always" returns the correct binary representation. ("Always" except for a known defect.)

I do not have the same degree of experience with ROUNDDOWN. But based on some random testing, it seems to always return the correct binary representation, as well.