Allocation Formulae

[MissyEd]

Hi

This little Excel problem is giving me a headache. Hopefully, someone can help

I have a fixed amount to distribute say dosh is £500.

I want to distribute this to a number of households say houses is 3 households.

Preferably, I want to distribute this as evenly as possible. But I also need to take into account the number of people in each house and each person must get an equal amount.

Each household has a maximim amount per person they can have, this is not the same for every household though, say maxdoshperperson could be £20 per person for one house and £30 per person.

Example

Total to distribute: £500
Total # of households: 7

Household..# persons..max per person..amt per person
A          2          30.00               
B          10         120.07
C          3          34.50

Any ideas ? Im at my wits end

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter

 

[Zathras]

That's not an Excel problem. That's a policy issue.

I would use multiple columns and do it in a series of steps.

First allocate the total amount by person, regardless of any maximum limits.
Then scrape off any overages and allocate to those who have not yet reached the max.
Then scrape off any overages from any of those in the second distribution and allocate to those remaining (if any) who have not yet reached the max.

You should probably be prepared to do that several times, using several columns across the worksheet. Eventually you will either run out of money or find everyone at their max with funds left over for you to pocket.

 

[SkipVought]

Hi,

Please be consistent with your explaination and example. You say 7 households, but your example has 3 households.

What's the deal?

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[MissyEd]

Sorry, I couldnt edit it once I'd posted it, initially I put 7 on but then I realised I'd have to type out 7 lines of examples *doh* Shame I can't create a formulae, it's a heck of a lot of work for several funds

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter

 

[Zathras]

Using the three data points in your original post, my algorithm produces the following allocations:
[tt]
A: 60
B: 338.4615.85
C: 101.3333333
Total: 479.7948718
[/tt]
I leave the rounding and adjusting up to you.

 

[bluedragon2]

If you want to be fair and give out the same percentage to each member then:

2 x 30 = 60
10 x 120.7 = 1207
3 x 34.50 = 103.50

add together for 60 + 1207 + 103.5 = 1370.50

find your percentage:

500.00 / 1370.50 = 36.48%

That is the percentage each person should get:

30 x 36.48% = 10.94 x 2 = 21.88
120.7 x 36.48% = 44.03 * 10 = 440.80
34.50 x 36.48% = 12.58 x 3 = 37.74

for a total of 500.42

You can find your percentage in one formula.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Had a math error in the last part:

30 x 36.48% = 10.94 x 2 = 21.88
120.7 x 36.48% = 44.03 * 10 = 440.30
34.50 x 36.48% = 12.58 x 3 = 37.74

for a total of 499.92

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[MissyEd]

Cheers guys I think Im close to a formula, just needs a little tweaking

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter

 

[Zathras]

That's why I said it is a policy issue. Any number of calculations are possible once it's determined what is wanted.

But does it seem fair to limit houshold "A" to 21.88 just because the maximum for household "B" is so much greater than for "A"?

Preferably, I want to distribute this as evenly as possible.

It seems to me that allocating simply on the basis of the maximums is contrary to that goal.

 

[bluedragon2]

I agree Zathras that larger households do not necessarily get more monies. And, there are many combinations that you could apply. The example I gave was just a percentage per person based on their allocated maximum.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...