#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 55
Default Sum + Round Up

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 915
Default Sum + Round Up

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default Sum + Round Up

"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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 915
Default Sum + Round Up

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


  #5   Report Post  
Posted to microsoft.public.excel.misc
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Round 1.45 down to 1.25 and 1.57 down to 1.50 joker007 Excel Worksheet Functions 2 October 3rd 07 01:25 AM
Round up or down cybergardener Excel Discussion (Misc queries) 7 April 11th 07 10:10 PM
Round up Becky Paine Excel Discussion (Misc queries) 10 June 12th 06 10:43 PM
Round near to Zero Tiya Excel Worksheet Functions 8 April 24th 06 10:31 PM
How do I ROUND() round off decimals of a column dataset? Højrup Excel Worksheet Functions 2 January 12th 05 11:50 AM


All times are GMT +1. The time now is 10:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"