Evenly Distribute £192 – smooth curve required..

[carphone]

Evenly Distribute £192 – smooth curve required..

Hi Everyone,

I have a mathematical problem I need to solve for a realwork problem. I have translated it into a non work related scenario as best I can.

The problem:
I have to share out £192 between 148 people based on a given priority factor each person has. The combined priority factor adds up to 100.The higher the priority factor the larger the share that person should receive. Each person must have at least £1 though.

148 people and £192 are variables that I may need to change n the future so would be great if the solution / formula would allow this.

Ideally I need to solve this in excel by ending up with alist of all 148 people in one column and then their share of the £192 in thenext column. I am looking for as smooth a curve as possible.

My first attempt is available and as you can see its a very linear attempt and has a nasty step in the graph. Please download my basic spreadsheet here to see my poor first attempt (also attached):

https://dl.dropboxusercontent.com/u/5601151/math challange.xls


I hope that make sense.

Any help would be amazing!

Many Thanks

Dan

 

[HallsofIvy]

If a total amount P is to be distributed among n people with the ith person getting a share \(\displaystyle a_i\), with \(\displaystyle a_i\) summing to 1, the ith person should be given \(\displaystyle a_iP\). The requirement that each person be given at least one dollar is a complication- it may not be possible to do both.

But what you can do is this: give each person an integer number of dollars. If \(\displaystyle a_iP\) may not an integer do that by dropping the "change" that person would get. Call the difference between \(\displaystyle a_iP\) and the amount the ith person gets (the next lower dollar amount) the "ith difference". That may give you some money left over. If, for any person, \(\displaystyle a_iP\) is less than one dollar, give one dollar out of that "left over" money.

Then either of two things may happen:
After giving one dollar to each person who had 0 before there is still money left over, give one dollar to the person with the largest "ith difference" until it is gone.
If, after giving out all of that money, there are still people who did not have one dollar, take one dollar from the person with the smallest "ith difference" to give to a person who does not yet have a dollar.

That may not satisfy the "proportion requirement"- it may be impossible to do that and insure that each person get at least one dollar. This method has been use by the United States government to allocate representatives.

For example, suppose $15 is to be distributed among 5 people with person A getting 20%, B getting 30%, C getting 5%, D getting 44%, and E getting 1%. 20% of $15 is $3, 30% of $15 is $4.50, 5% of $15 is $0.75, 44% of $15 is $6.60, and 1% of $15 is $0.15. Those add to exactly $15 because the percentages add to 100%.

So, initially, A is given $3, B is given $4, C is given $0, D is given $6, and E is given $0. That adds to 3+ 4+ 6= $13 so there are $2 left over. Give $1 each to C and E.

 

[carphone]

Thanks for the quick response!

On reflection, my problem would have been better phrased "to share out the funds out as fairly as possible". acknowledging that the final solution would involve compromise some where.

I will have a play with your suggestion. I was kind of hoping a clever formula could be applied to meet the brief / solution exactly. Maybe not so simple...

Any other approaches I could take?

Thanks again!

Dan

 

[HallsofIvy]

Yes, I didn't look at the specific numbers but Denis has a very good point. First give $1 to each person, leaving $44 to be distributed. Give one dollar to each of the 44 people with the highest "priority factors".