ROUND AND SUM

[renwaters]

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

 

[ker_01]

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

 

[JoeU2004]

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 -----

 

[JoeU2004]

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 -----

 

[renwaters]

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]

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 -----