ROUNDDOWN function produces different results?

[Baldbloke]

Hi

Excel 2003 SP2... can anyone help? Using ROUNDDOWN function, I am getting
two different results from the same data

The spreadsheet I'm using has data entered thus. I can't post the actual
spreadsheet here, but can mail it if anyone's interested!

To replicate the error, enter these as values in the cells as follows:
20.4 in A4; 0.8 in A5, 400in A7, 300 in A8, 4 in A11 and 3 in A12.

Excluding the quotes, enter these formulae: "=A7*A4/A8" in cell D7;
"=D7/A5/5-6" in F7; "ROUNDDOWN (F7,1)" in H7. This will give a result in H7
of 0.7000

Now enter these: "=A11*A4/A12" in D11; "=D11/A5/5-6" in F11; "ROUNDDOWN
(F11,1)" in H11. This will give a result in H11 of 0.8000

The problem is that although the results of the calculations in both columns
D and F return the same result, the ROUNDDOWN function creates two different
results, even though the data referred to in each ROUNDDOWN formula is of the
same values. The result in cell H11 is correct, the result in H7 is not.

The cells are formatted as general.

Any ideas?

Thanks

BB

 

[Mike H]

Hi,

Format H7 and H11 to have lots of decimal places (30) and you'll see the
answer.

Mike

 

[Mike H]

Sorry I meant F7 and F11

 

[Don Guillett]

=ROUNDDOWN(D7/A5,1)/5-6

 

[Baldbloke]

Thanks for the reply, however this produced an odd result as well! I know
this is moving away from the original post, but I think it's worth mentioning
..

To test it, on a clean sheet, I formatted cells A1, B1, A3 and B3 as number
to 30 decimal places, then entered these formulae:

In A1: (400*20.4)/1200
In B1: A1-6

This returns an answer of 0.799999999999990000000000000000, which is clearly
incorrect for a simple arithmetic function!

However (bearing in mind the formatting of the cells) if I enter a plain
numeric value of 6.8 in cell A3 and enter "=A3-6" (no quotes) in cell B3,
this returns the answer of 0.8, which is correct.

Why would the formula in cell A1 affect the result by 0.000000000000001? Not
that I work to this level of accuracy, but someone out there might!

Very strange!

BB

 

[Jerry W. Lewis]

The math is right, but you are seeing the impact of unavoidable
approximations to your inputs.

Most computer software (including Excel) does binary math. In binary, most
terminating decimal fractions (such as .4 and .8) have no exact
representation, and hence must be approximated (just as 1/3 must be
approximated as a decimal fraction). When you do math with approximate
inputs, you should not be surprised to get an approximate result.

As documented in Excel's Help, the representation of numbers is only
accurate to 15 figures. You should not assume that figures beyond the 15th
are what you expect. For example, when you enter 20.4, you actually get
20.39999999999999857891452847979962825775146484375
which is the closest possible binary value given the number of bits used by
Excel (and almost all other computer software, since Excel follows the IEEE
standard for double precision here). You cannot directly see this
approximation, since Excel will not natively display more than 15 figures (if
you ask for more, it will pad the display with meaningless zeros). But you
can detect the approximation by subtraction of a nearly equal number; for
example =20.4-20 returns
0.39999999999999857891452847979962825775146484375
which correctly displays to 15 figures as 0.399999999999999. For more
precise display of what is going on under the hood, see my conversion
functions at
http://groups.google.com/group/micr..._frm/thread/9f83ca3dea38e501/6efb95785d1eaff5

You incorrectly assume that your two original calculations are equivalent,
but your multipliers are of different orders of magnitude, which results in
different losses of precision to intermediate values.

Jerry

 

[Jerry W. Lewis]

Here is a simple decimal analog of what happened to you in binary.

Your surprise was that the following two expression are not numerically
equialent in finite precision binary calculations even though they are
mathematically equivalent
=(20.4*400)/1200-6
=(20.4*4)/12-6

A analogous decimal problem would be
=(0.6667*1003)/3009
=(0.6667*59)/177
You can factor 17 out of 1003 and 3009 to mathematically reduce the first
problem to the second; thus they are mathematically equivalent. But if you
perform these calculations numerically on a hypothetical decimal computer
that only carries 4 figures, then you would get
668.7/3009 = 0.2222
39.34/177 = 0.2223

That you can factor out terms is irrelevant to understanding how rounding
will impact intermediate results that need more than the available precision.

Jerry