Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ROUND AND SUM
Hello
I want to round money that is in a pot to pay out 6 places. 877.20 530.40 244.80 163.20 122.40 102.00 sum = 2040.00 I want to round but the sum can't be less than 2040 or more than 2040. Please help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ROUND AND SUM
I think you'll have to provide some more information; given your sample
numbers, one of your .40 items will have to be rounded down, the other rounded up in order to meet your criteria. Are these in order of importance, where the higher percentage item (26%) would be the ideal item to round up in a tie, and the lower item (6%) would be the one to round down? What if your results were to have each of the first 5 numbers end in .2, what logic would you use to decide who gets the extra, and who loses out? If this is a money pot, why round it at all- why not just pay the amounts listed (other than convenience)? What rules are you bound by- can you just round everyone down and put the remainder in the next pot? Alternatively, instead of rounding the dollar values directly, can you round the source percentages to the nearest 5?? That would give you (at least in this instance) whole number payouts that sum to 2040. The individual payouts would vary from your original number anywhere from 0 to ~41 dollars, but again, I don't know if your rules permit this type of adjustment. HTH, Keith "renwaters" wrote: Hello I want to round money that is in a pot to pay out 6 places. 877.20 530.40 244.80 163.20 122.40 102.00 sum = 2040.00 I want to round but the sum can't be less than 2040 or more than 2040. Please help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ROUND AND SUM
"renwaters" wrote:
I want to round money that is in a pot to pay out 6 places. Rounded how? I assume you mean you want each individual number to be rounded to an integer. Right? I want to round but the sum can't be less than 2040 or more than 2040. Which is to say: you want the sum of the rounded numbers to be equal to the rounded total. Right? Try the following.... If the numbers are in A1:A6, then: B1: =round(A1,0) B2: =round(sum($A$1:A2),0) - sum($B$1:B1) Copy B2 down through B6. With your numbers, the result is: 877 531 244 164 122 102 I know what you're thinking: shouldn't 530.40 and 163.20 round to 530 and 163; and shouldn't 244.80 round to 245? As you may know, it simply will not work to round the individual numbers. Consider the following extreme examples. a. 10 numbers all of which are 0.2. Two people must get 1 each. Which ones? It's arbitrary. b. 10 numbers all of which are 0.6. Only 6 people can get 1 each; 4 people must get zero. Which ones? Again, it's arbitrary. The algorithm I describe above makes those decisions in an egalitarian manner. You could reorder some numbers to try to minimize the surprises. For example, put 244.80 first. But I think there will likely always be some surprises due the quantization requirement. ----- original message ----- "renwaters" wrote in message ... Hello I want to round money that is in a pot to pay out 6 places. 877.20 530.40 244.80 163.20 122.40 102.00 sum = 2040.00 I want to round but the sum can't be less than 2040 or more than 2040. Please help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ROUND AND SUM
I wrote:
B1: =round(A1,0) B2: =round(sum($A$1:A2),0) - sum($B$1:B1) I tried the following formula in an attempt to favor rounding up whenever appropriate: B2: =IF(AND(ROUND(A2,0)A2, SUM($B$1:B1,ROUND(A2,0))<=ROUND(SUM($A$1:$A$6),0)) , ROUND(A2,0), MAX(0,ROUND(SUM($A$1:A2),0)-SUM($B$1:B1))) With your original numbers, that yields: 877 831 245 163 122 102 Note that 244.80 now rounds to 245, as you might prefer. That formula also works well with the two extreme examples that I mentioned: 10 numbers all 0.2, and 10 numbers all 0.6. However, consider the following results, with the unrounded numbers on the left and the rounded numbers on the right: 877.90 878 530.90 531 244.80 245 163.90 164 122.50 123 102.40 101 Note that 102.40 is "rounded" to 101 (!). I think my original formula would avoid that in general, although I have not proved it. For this example, 122.50 and 102.40 are rounded to 122 and 102. ----- original message ----- "JoeU2004" wrote in message ... "renwaters" wrote: I want to round money that is in a pot to pay out 6 places. Rounded how? I assume you mean you want each individual number to be rounded to an integer. Right? I want to round but the sum can't be less than 2040 or more than 2040. Which is to say: you want the sum of the rounded numbers to be equal to the rounded total. Right? Try the following.... If the numbers are in A1:A6, then: B1: =round(A1,0) B2: =round(sum($A$1:A2),0) - sum($B$1:B1) Copy B2 down through B6. With your numbers, the result is: 877 531 244 164 122 102 I know what you're thinking: shouldn't 530.40 and 163.20 round to 530 and 163; and shouldn't 244.80 round to 245? As you may know, it simply will not work to round the individual numbers. Consider the following extreme examples. a. 10 numbers all of which are 0.2. Two people must get 1 each. Which ones? It's arbitrary. b. 10 numbers all of which are 0.6. Only 6 people can get 1 each; 4 people must get zero. Which ones? Again, it's arbitrary. The algorithm I describe above makes those decisions in an egalitarian manner. You could reorder some numbers to try to minimize the surprises. For example, put 244.80 first. But I think there will likely always be some surprises due the quantization requirement. ----- original message ----- "renwaters" wrote in message ... Hello I want to round money that is in a pot to pay out 6 places. 877.20 530.40 244.80 163.20 122.40 102.00 sum = 2040.00 I want to round but the sum can't be less than 2040 or more than 2040. Please help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ROUND AND SUM
Thank you I have a few ways now to work with this. The pay outs are for a
poker league to having change on hand would be a pain. Instead of round I did Mround of 5 to cut having to have ones on hand to. Thanks agian "JoeU2004" wrote: I wrote: B1: =round(A1,0) B2: =round(sum($A$1:A2),0) - sum($B$1:B1) I tried the following formula in an attempt to favor rounding up whenever appropriate: B2: =IF(AND(ROUND(A2,0)A2, SUM($B$1:B1,ROUND(A2,0))<=ROUND(SUM($A$1:$A$6),0)) , ROUND(A2,0), MAX(0,ROUND(SUM($A$1:A2),0)-SUM($B$1:B1))) With your original numbers, that yields: 877 831 245 163 122 102 Note that 244.80 now rounds to 245, as you might prefer. That formula also works well with the two extreme examples that I mentioned: 10 numbers all 0.2, and 10 numbers all 0.6. However, consider the following results, with the unrounded numbers on the left and the rounded numbers on the right: 877.90 878 530.90 531 244.80 245 163.90 164 122.50 123 102.40 101 Note that 102.40 is "rounded" to 101 (!). I think my original formula would avoid that in general, although I have not proved it. For this example, 122.50 and 102.40 are rounded to 122 and 102. ----- original message ----- "JoeU2004" wrote in message ... "renwaters" wrote: I want to round money that is in a pot to pay out 6 places. Rounded how? I assume you mean you want each individual number to be rounded to an integer. Right? I want to round but the sum can't be less than 2040 or more than 2040. Which is to say: you want the sum of the rounded numbers to be equal to the rounded total. Right? Try the following.... If the numbers are in A1:A6, then: B1: =round(A1,0) B2: =round(sum($A$1:A2),0) - sum($B$1:B1) Copy B2 down through B6. With your numbers, the result is: 877 531 244 164 122 102 I know what you're thinking: shouldn't 530.40 and 163.20 round to 530 and 163; and shouldn't 244.80 round to 245? As you may know, it simply will not work to round the individual numbers. Consider the following extreme examples. a. 10 numbers all of which are 0.2. Two people must get 1 each. Which ones? It's arbitrary. b. 10 numbers all of which are 0.6. Only 6 people can get 1 each; 4 people must get zero. Which ones? Again, it's arbitrary. The algorithm I describe above makes those decisions in an egalitarian manner. You could reorder some numbers to try to minimize the surprises. For example, put 244.80 first. But I think there will likely always be some surprises due the quantization requirement. ----- original message ----- "renwaters" wrote in message ... Hello I want to round money that is in a pot to pay out 6 places. 877.20 530.40 244.80 163.20 122.40 102.00 sum = 2040.00 I want to round but the sum can't be less than 2040 or more than 2040. Please help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ROUND AND SUM
"renwaters" wrote:
Instead of round I did Mround of 5 to cut having to have ones on hand to. Okay. I will assume that the total (e.g. 2040) is a multiple of 5. Then my original suggestion becomes: B1: =MROUND(A1,5) B2: =MROUND(SUM($A$1:A2),5) - SUM($B$1:B1) I developed a better suggestion in another thread about a similar problem. Assuming that the amounts to be rounded are sorted in descending order, as yours are, then I would suggest: B1: =MAX(5, MROUND(A1,5)) B2: =MIN(MAX(5, MROUND(($A$9-SUM($B$1:B1))*A2/SUM(A2:$A$6),5)), $A$9-SUM($B$1:B1)) where A9 is the total (e.g. 2040). Theory of operation: Allocation is based on the percentage of the remainder. MAX(5,...) adjusts upward the first allocations less than 5. The MIN() expression ensures that the artificial adjustments do not cause the sum to exceed the total. To see the benefit, consider a total of 30 with the same distribution of unrounded amounts as a percentage of the total, namely: 43%, 26%, 12%, 8%, 6% and 5%. My original suggestion would yield the following allocation: 12.90 15 7.80 5 3.60 5 2.40 0 1.80 5 1.50 0 Note the out-of-order allocation for 1.8, which is counter-intuitive. I warned about that in my original posting of that suggestion. My new suggestion avoids that. It yields the following allocation: 12.90 15 7.80 5 3.60 5 2.40 5 1.80 0 1.50 0 Arguably, the "7.80" person might complain that he should get 10, not 5. The reason why he got only 5 is: after allocating 15 to the "12.90" person, there are only 15 left, and the "7.80" person gets 7.8/17.1 (30 - 12.90) of 15 mrounded -- about 6.84, not 7.80. It is tempting to want to change my new formula so that we simply mround the unrounded allocations. That is: B2: =MIN(MAX(5, MROUND(A2,5)), $A$9-SUM($B$1:B1)) In fact, that will seem to work in many/most cases. But if we do that, the sum of the mrounded allocations will not always equal the total. For example, if the total is 85, this bogus formula yields the following allocation: 36.55 35 22.10 20 10.20 10 6.80 5 5.10 5 4.25 5 which sums to 80 instead of 85. I have not thought of any way to satisfy both desirable requirements, namely: allocate based on the unrounded amounts, and the sum exactly equals the total allocation. ----- original message ----- "renwaters" wrote in message ... Thank you I have a few ways now to work with this. The pay outs are for a poker league to having change on hand would be a pain. Instead of round I did Mround of 5 to cut having to have ones on hand to. Thanks agian "JoeU2004" wrote: I wrote: B1: =round(A1,0) B2: =round(sum($A$1:A2),0) - sum($B$1:B1) I tried the following formula in an attempt to favor rounding up whenever appropriate: B2: =IF(AND(ROUND(A2,0)A2, SUM($B$1:B1,ROUND(A2,0))<=ROUND(SUM($A$1:$A$6),0)) , ROUND(A2,0), MAX(0,ROUND(SUM($A$1:A2),0)-SUM($B$1:B1))) With your original numbers, that yields: 877 831 245 163 122 102 Note that 244.80 now rounds to 245, as you might prefer. That formula also works well with the two extreme examples that I mentioned: 10 numbers all 0.2, and 10 numbers all 0.6. However, consider the following results, with the unrounded numbers on the left and the rounded numbers on the right: 877.90 878 530.90 531 244.80 245 163.90 164 122.50 123 102.40 101 Note that 102.40 is "rounded" to 101 (!). I think my original formula would avoid that in general, although I have not proved it. For this example, 122.50 and 102.40 are rounded to 122 and 102. ----- original message ----- "JoeU2004" wrote in message ... "renwaters" wrote: I want to round money that is in a pot to pay out 6 places. Rounded how? I assume you mean you want each individual number to be rounded to an integer. Right? I want to round but the sum can't be less than 2040 or more than 2040. Which is to say: you want the sum of the rounded numbers to be equal to the rounded total. Right? Try the following.... If the numbers are in A1:A6, then: B1: =round(A1,0) B2: =round(sum($A$1:A2),0) - sum($B$1:B1) Copy B2 down through B6. With your numbers, the result is: 877 531 244 164 122 102 I know what you're thinking: shouldn't 530.40 and 163.20 round to 530 and 163; and shouldn't 244.80 round to 245? As you may know, it simply will not work to round the individual numbers. Consider the following extreme examples. a. 10 numbers all of which are 0.2. Two people must get 1 each. Which ones? It's arbitrary. b. 10 numbers all of which are 0.6. Only 6 people can get 1 each; 4 people must get zero. Which ones? Again, it's arbitrary. The algorithm I describe above makes those decisions in an egalitarian manner. You could reorder some numbers to try to minimize the surprises. For example, put 244.80 first. But I think there will likely always be some surprises due the quantization requirement. ----- original message ----- "renwaters" wrote in message ... Hello I want to round money that is in a pot to pay out 6 places. 877.20 530.40 244.80 163.20 122.40 102.00 sum = 2040.00 I want to round but the sum can't be less than 2040 or more than 2040. Please help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Round | Excel Worksheet Functions | |||
Round Up and Round Down Time | Excel Worksheet Functions | |||
round down? | Excel Discussion (Misc queries) | |||
VB Function Round vs Excel function Round not behaving the same Od | Excel Programming | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions |