Allocation based on individual unrelated scores

[darbatross]

I work in a university study abroad office and have been left with the wreckage of my predecessor, so I'm trying to establish new procedures and methods for processing scholarship applications to make them as objective as possible. I have no idea how to begin with this problem, though I think it's decently simple with the right tools - hopefully you guys can help!

Okay, so let's say I have 10 students. Each student has been assigned a "score" (a percentage grade out of 100% summarizing a number of different influences, including FAFSA need, GPA, essay responses, etc.). Each students' score is unrelated to the others' scores, but the score should reflect financial need and academic worth.

Here's the key part I need help with: I have $20,000 in funding to distribute across these 10 students. I need to use the majority of the money (final penny isn't crucial) and it needs to be allocated based on each student's relative grade. As in, Student 1 gets $4000 while Student 6 gets $500, etc.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Student[/TD]
[TD]Score[/TD]
[TD]Suggested Funding ($20,000)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]98.70%[/TD]
[TD]wicked awesome formula here[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]72.62%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]77.94%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]47.79%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]66.16%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]60.04%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]75.74%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]75.00%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]62.13%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]91.70%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[BKrukowski]

=Trunc(20000*(Student Score / Sum of all Students Score))

Trunk prevents fractions of dollars because it don't matter if you give out all the pennies

This will always use the full amount and assigns the money based on the actual students score compared to the total scores.
This is the result for the above data. From this you have a 5 dollar surplus not give out, you can choose to add it to the highest score or don't use the Trunc function and include change in the award.
Student 1 = 2712
Student 2 = 1995
Student 3 = 2141
Student 4 = 1313
Student 5 = 1818
Student 6 = 1649
Student 7 = 2081
Student 8 = 2060
Student 9 = 1707
Student 10 = 2519

 

[darbatross]

Thank you so much! This is perfect. And just as expected, simple enough if you know the formula!