ROUNDDOWN Excell function

[Guest]

I can't get ROUNDDOWN to work with embedded operations.
For instance:
=ROUNDDOWN(7-1,0) returns 6
BUT
=ROUNDDOWN(((7-1)/3),0) will return 1 instead of 2

what is going on?

 

[Bob Phillips]

I get 2. Excel 2000, XP Pro

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi

It quite rightly returns 2 for me. Are you sure that's what you have
typed in your cell? What happens if you press F9?

 

[JE McGimpsey]

What version of XL are you using?

=ROUNDDOWN(((7-1)/3),0)

returns 2 in Mac XL04.

If instead of 7, 1 and 3, you're using references to cells with
formulae, it's possible that there are rounding errors in your formulae
that are causing the ROUNDDOWN() to round to 1.

For instance, if the cell displaying 7 instead contains a formula
returning

6.999999999999993

due to rounding errors, the ROUNDDOWN will operate on the stored value,
rather than the displayed value.

 

[Guest]

As other's have noted, no version of Excel behaves as you describe. Assuming
that you have simplified from calculated values to constants as the arguments
to ROUNDDOWN, you may find the D2D() function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to be helpful in understanding.

Excel will display no more than 15 digits, but it takes 17 digits to
uniquely identify an IEEE standard double precision number. Excel (and
almost all general purpose software) follows the IEEE standard for double
precision storage of binary numbers. Most terminating decimal fractions are
non-terminating binary fractions that can only be approximated (just as 1/3
can only be approximated in decimal). As a result, floating point
calculations that you may think should result in an integer result, may
actually have a very small difference from that result due to binary
approximation. Normally this has no more impact on calculations than the
expected approximations associated with irrational numbers or non-terminating
decimal fractions, but it can be magnified by rounding to produce unexpected
results.

To work around this unavoidable consequence of finite precision binary math,
you might need to include a protective fuzz factor. For instance, if the
numerator calculation should produce a 3 decimal place result, then you might
use something like
=ROUNDDOWN((calc+.00001)/3,0)

Jerry