Thread: Sum + Round Up
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
smartin smartin is offline
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