Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Round 1.45 down to 1.25 and 1.57 down to 1.50 | Excel Worksheet Functions | |||
Round up or down | Excel Discussion (Misc queries) | |||
Round up | Excel Discussion (Misc queries) | |||
Round near to Zero | Excel Worksheet Functions | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions |