I've run across an interesting problem that I don't know how to
solve in Excel. I'm not sure it can be resolved.

I'm using a metaphor for the situation to simplify things a bit.
Let's say 6 people, a, b, c, d, e, f are given $1000 to share. Each
gets his/her share based on some external scoring system, say from 1 to
7 (we don't have to worry about the scoring system, that's someone
else's problem). Each person's share will be based on his score as
a fraction of the total score (If a's score is 3 and the total score
is 20, a gets $150). Pretty simple, so far. Here's the problem. What
if the agreement states than no participant gets less than some minimum
amount?

A B C D
E
Base Unit Share First Cut Share Actual Share
a 2 100.00 130.00 130.00
b 5 250.00 250.00 203.33
c 2 100.00 130.00 130.00
d 1 50.00 130.00 130.00
e 7 350.00 350.00 284.67
f 3 150.00 150.00 122.00
20 1000 3 1000

(I hope this is legible after I post it)

The First Cut Share is just their proportional part of the $1000 pot.
The second cut is done by replacing a, c, and d's shares with the
minimum, $130. =IF(C4<130,130,C4)
This then gives each person's Actual Share, by subtracting the $130
allocations from the total pot and then divvying up the remainder of
the pot based on the remaining participants shares.
=IF(D4=130,130,$E$2/$E$E10*B4)
Or it should, but f's share, which was over the $130 minimum at $150
has now slipped below the minimum to $122.

I can see that this can possibly go on endlessly.

I doubt very much that this is a first in the history of
math/spreadsheets. It seems to me like it's probably a classic in
some discipline, just one of the many I've never explored. Is there a
solution to this puzzle?

POT: 1000 Remainder: 220 Revised
Original 1st Cut Scores Above Spread Remainder
a 2 100 130 0 0.0 130.0
b 5 250 130 5 73.3 203.3
c 2 100 130 0 0.0 130.0
d 1 50 130 0 0.0 130.0
e 7 350 130 7 102.7 232.7
f 3 150 130 3 44.0 174.0
Tot 20 1000 780 15 220.0 1000.0

give everyone 130 and then divvy the remainder proportionally from the
subset of people who are above the minimum.

--
Regards,
Tom Ogilvy


"davegb" wrote:

I've run across an interesting problem that I don't know how to
solve in Excel. I'm not sure it can be resolved.

I'm using a metaphor for the situation to simplify things a bit.
Let's say 6 people, a, b, c, d, e, f are given $1000 to share. Each
gets his/her share based on some external scoring system, say from 1 to
7 (we don't have to worry about the scoring system, that's someone
else's problem). Each person's share will be based on his score as
a fraction of the total score (If a's score is 3 and the total score
is 20, a gets $150). Pretty simple, so far. Here's the problem. What
if the agreement states than no participant gets less than some minimum
amount?

A B C D
E
Base Unit Share First Cut Share Actual Share
a 2 100.00 130.00 130.00
b 5 250.00 250.00 203.33
c 2 100.00 130.00 130.00
d 1 50.00 130.00 130.00
e 7 350.00 350.00 284.67
f 3 150.00 150.00 122.00
20 1000 3 1000

(I hope this is legible after I post it)

The First Cut Share is just their proportional part of the $1000 pot.
The second cut is done by replacing a, c, and d's shares with the
minimum, $130. =IF(C4<130,130,C4)
This then gives each person's Actual Share, by subtracting the $130
allocations from the total pot and then divvying up the remainder of
the pot based on the remaining participants shares.
=IF(D4=130,130,$E$2/$E$E10*B4)
Or it should, but f's share, which was over the $130 minimum at $150
has now slipped below the minimum to $122.

I can see that this can possibly go on endlessly.

I doubt very much that this is a first in the history of
math/spreadsheets. It seems to me like it's probably a classic in
some discipline, just one of the many I've never explored. Is there a
solution to this puzzle?

Tom Ogilvy wrote:
POT: 1000 Remainder: 220 Revised
Original 1st Cut Scores Above Spread Remainder
a 2 100 130 0 0.0 130.0
b 5 250 130 5 73.3 203.3
c 2 100 130 0 0.0 130.0
d 1 50 130 0 0.0 130.0
e 7 350 130 7 102.7 232.7
f 3 150 130 3 44.0 174.0
Tot 20 1000 780 15 220.0 1000.0

give everyone 130 and then divvy the remainder proportionally from the
subset of people who are above the minimum.

--
Regards,
Tom Ogilvy



Doh! Thanks Tom!

"davegb" wrote:

I've run across an interesting problem that I don't know how to
solve in Excel. I'm not sure it can be resolved.

I'm using a metaphor for the situation to simplify things a bit.
Let's say 6 people, a, b, c, d, e, f are given $1000 to share. Each
gets his/her share based on some external scoring system, say from 1 to
7 (we don't have to worry about the scoring system, that's someone
else's problem). Each person's share will be based on his score as
a fraction of the total score (If a's score is 3 and the total score
is 20, a gets $150). Pretty simple, so far. Here's the problem. What
if the agreement states than no participant gets less than some minimum
amount?

A B C D
E
Base Unit Share First Cut Share Actual Share
a 2 100.00 130.00 130.00
b 5 250.00 250.00 203.33
c 2 100.00 130.00 130.00
d 1 50.00 130.00 130.00
e 7 350.00 350.00 284.67
f 3 150.00 150.00 122.00
20 1000 3 1000

(I hope this is legible after I post it)

The First Cut Share is just their proportional part of the $1000 pot.
The second cut is done by replacing a, c, and d's shares with the
minimum, $130. =IF(C4<130,130,C4)
This then gives each person's Actual Share, by subtracting the $130
allocations from the total pot and then divvying up the remainder of
the pot based on the remaining participants shares.
=IF(D4=130,130,$E$2/$E$E10*B4)
Or it should, but f's share, which was over the $130 minimum at $150
has now slipped below the minimum to $122.

I can see that this can possibly go on endlessly.

I doubt very much that this is a first in the history of
math/spreadsheets. It seems to me like it's probably a classic in
some discipline, just one of the many I've never explored. Is there a
solution to this puzzle?