Need an Excel formula for distribution based on points 1

[con10t]

I am working on a funding spreadsheet. We have applicants who have been scored "points". We have $100,000 to allocate to them. The person with the lowest points will receive at least $500.00 The person with the highest points will receive $4,000. We would like to distribute the remainder of the funds based on point value. How would I set up a formula to calculate this? TIA

Deborah

 

[ridge]

How many people are involved?

 

[con10t]

This year there are 20 people. Next year there may be more. Points this year range from 22 to 100. Person with 22 points gets $500; person with 100 points gets $4,000. Is there some kind of sliding scale function in Excel that will appropriately allocate the rest of the funds?

 

[ridge]

I think I formulated a excel sheet example for you...
Mind if I email it to you?..Email address?

 

[con10t]

Thanks you're a lifesaver--dcspublicATSIGNattDOTnet

Trying to keep junk mail volume to a low roar. I'm getting e-mail for body parts I don't have!! LOL!! Hope you understand.

Deborah

 

[ridge]

oh yes...I understand indeed...
The only problem i had with the sheet was the total for the highest points getter...with 100K to dispurse, and only 20 people, thats only 80k...Eith you'll have to give the top dog more, or reduce the total funds...

 

[con10t]

Thanks so much--and you're right! Either boss man has given me bad numbers or somebody's going to get a nice scholarship. I'd love to see the example. Let's drop total down to 10K and see how that works. Thanks!

Deborah

 

[ridge]

This example does not have a maximum or minimum earned...This gives the payoff to the % or total points
earned by each person individually...
A B C
1 Name Points Earned
2 Alex 325 =(10000/B12)*B2
3 Bertha 220 =(10000/B12)*B3
4 Carol 415 =(10000/B12)*B4
5 Dexter 297 =(10000/B12)*B5
6 Edith 324 =(10000/B12)*B6
7 Faye 460 =(10000/B12)*B7
8 Greg 199 =(10000/B12)*B8
9 Henry 517 =(10000/B12)*B9
10 Inez 466 =(10000/B12)*B10
11 John 470 =(10000/B12)*B11
12 Total Points sum(B2:B11) =sum(C2:C11)<should equal
$10,000

 

[con10t]

Thanks! I'll run this past the big guy and see if he really wants to set maximum and minimum. I appreciate your help!

Deborah

 

[ridge]

How did things work out for you?

 

[con10t]

Ridge--Thank you for following up with me. I have been remiss in giving you kudos and I apologize for not thanking you for your help.

The big guy was unhappy with how the spread turned out, so we added another formula--the top 10 get the lion's share and the rest get what's left. So we used your basic formulas after determining the percentage of who gets what. Without your help we wouldn't have been able to do that. Thanks very much!!

Have a great Memorial Day!