Thread: Sum + Round Up
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default Sum + Round Up

"smartin" wrote:
There appears to be an unfortunate binary-to-decimal conversion
in the expression 10.1 - 10
The result seen by the formula evaluator is 0.0999...96


Right. Unfortunate; but by no means unusual.

And while that particular binary arithmetic results in an understated value,
there is equal probability of resulting in an overstated value, which might
yield surprising results from CEILING.

Moreover, these numerical aberrations, which might yield surprising results
from CEILING or FLOOR, do not always need to be visible when looking at the
first 15 significant digits, Excel's formatting limit.

And conversely, not all aberrations that can be seen in the first 15
significant digits yield suprising results from CEILING or FLOOR.

Finally, given an example of an integer and decimal fraction pair that
yields surprising results from CEILING or FLOOR, a different integer paired
with the same decimal fraction paired (within the first 15 significant
digits) might behave as expected with CEILING and/or FLOOR, and vice versa.

All of this has to do with, in part, the relative magnitudes of the numbers
involved in the arithmetic, the algorithm for converting from binary to
numeric string, and the fact that floating-point arithmetic on
Intel-compatible CPUs has greater precision than the stored floating-point
result.

And all of those factors make it almost impossible to predict which
expressions will and will not have surprising results from CEILING or FLOOR,
as well as other arithmetic expressions.

Wow, that's a mouthful! If you are still interested (or ever were ;-), the
following are some examples of each of the points above. Although they are
somewhat contrived, I am quite certain that each can arise "naturally" (like
10.1 - 10 v. 0.1).


1. Surprising result from CEILING or FLOOR with numerical aberration not
visible in the first 15 significant digits.

Consider 0.45 + 5*2^-54 in A1. Excel formats that as 0.450...0 to 15
significant digits. But CEILING(100*A1,5)/100 is 0.50, not 0.45 as
expected.


2. Expected result from CEILING or FLOOR despite numerical aberration
visible in the first 15 significant digits.

Note that 10.1 - 10 is 0.1 - 26*2^-56, which Excel formats as 0.09...96
to 15 significant digits. CEILING(100*A1,5)/100 is 0.05, not 0.10 as
expected.

But consider 0.1 - 18*2^-56 in A1, which Excel formats as 0.09...98 to
15 significant digits. CEILING(100*A1,5)/100 is 0.10 as expected.


3. Result from CEILING or FLOOR with one integer and decimal fraction pair
differs from the result with a different integer paired with the same
decimal fraction (within 15 significant digits).

The point I am trying to make is, for example, 0.45 is overstated in the
internal representation
(0.450000000000000,0111022302462515654042363166809 08203125),
whereas 12.45 is understated in the internal representation
(12.4499999999999,99289457264239899814128875732421 875).

I believe that creates the __potential__ for different behaviors in
arithmetic expressions.

For example, if A1 is 0.45 + 4*2^-54
(0.450000000000000,2331468351712828734889626502990 72265625),
CEILING(100*A1,5)/100 yields 0.45 as expected.

But if A1 is 12.45 + 14*2^-49
(12.4500000000000,24158453015843406319618225097656 25), CEILING(100*A1,5)/100
yields 12.50, which is unexpected.

Note that in both cases, Excel formats the decimal fraction as .450...0
to 15 significant digits (including the integer part; so 12.45 has fewer
decimal places). Also note that the decimal fraction digits beyond 15
significant digits are "close".

Purists might argue that the decimal fractions are significantly
different, and I really should compare the 12.45 aberration with 0.45 +
435*2^-54 in A1 (0.450000000000024,1584530158434063196182250976562 5), which
has the equivalent "unexpected" CEILING result (0.50). (It is unexpected
only when A1 is formatted to 13 decimal places or less.) And they are
right.

But I am trying to make a point about differences that are not visible
within the first 15 significant digits; differences that, therefore, are
unknown to most Excel users and mislead their expectations.


----- original message -----

"smartin" wrote in message
...
Good call, JoeU2004. There appears to be an unfortunate binary-to-decimal
conversion in the expression
10.1 - 10

The result seen by the formula evaluator is 0.0999...96

instead of 0.1, and of course FLOOR knocks the result down.


JoeU2004 wrote:
"smartin" wrote:
Try this:
=CEILING(100*(A4*$B$3),5)/100

Or maybe this if you intend to sum a range:
=CEILING(100*SUM(A4:A6)*$B$3,5)/100


Be careful with that. I suspect that surprises are possible. I cannot
think of a naturally-occuring example using CEILING, but here is an
example using FLOOR:

=FLOOR(100*(10.1 - 10), 5) / 100

results in 0.05 instead of 0.10 as you might expect since 10.1 - 100
should be and is normally displayed as 0.10, a number that already ends
in a multiple of 5.

(But "naturally-occuring", I mean without adding multiples of
infinitesimal powers of two, as I often do here.)

I would do:

=CEILING(ROUND(100*A4*$B$3, 0), 5) / 100

to ensure that WYSIWYG, since Canon is working dollars and cents.

Note: This presumes, as SMartin does, that when Canon wrote "next 5", he
means "next multiple of 5", which includes numbers that end in zero, not
the next highest number with 5 in the 1/100th position.


----- original message -----

"smartin" wrote in message
...
Canon wrote:
Excel 2007
Using the following formula, =sum(A4*$B$3), I would like the amount to
always round up to the next 5
ie: if the true answer is $45.12, I would like the cell to show $45.15

Try this:
=CEILING(100*(A4*$B$3),5)/100

Or maybe this if you intend to sum a range:
=CEILING(100*SUM(A4:A6)*$B$3,5)/100