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