D
davegb
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?
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?