Formula or script help

[aminnuto]

Hi.

I need a way to calculate this...

The scenario is:
I have 100 sales people.

In a month, Each of these sales people produce sales that result in the company with 100.00 net profit.

We have each sales person on a list that is sorted Most to Least profit generated.

We want to divide that 100.00 amongst all 100 sales people but weighted proportionate to their positon on this list. The higher their Rank (lowest number), the higher the share of the Bonus amount they should get.


The total cash distributed will be 100.00 but it should be distributed more to the highest person and least to the lowest person. Everyone in between gets a share that is increased or decreased depending on their Rank.


So, the question is, how would I create that in Excel?

 

[aminnuto]

Neverminded. I figured it out.

 

[Peter_SSs]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.