Weighted formula

[aminnuto]

Hi.

I want to send a bonus to my top 100 sales people but I want the person in the #1 position to get the most and #2 to get the 2nd most etc... down to the 100th person getting the least.

So, If i choose to bonus out 100.00 to all 100 people, what formula would I use to make sure the higher performing sales people get the most (percentage) and the lowest performing sales people get the least portion of the 100.00 bonus amount?

 

[Joe4]

You will need to be more detailed than that. How exactly is the amount to be determined? What is the logic?
There are many different ways it could be done.

For example, person 1 could receive half of the bonus, then person 2 could receive half of that, and person 3 could receive half of that, etc.
Or it could be down incrementally, i.e. person 1 receives 100 "parts", person 2 receives 99 "parts", person could receive 98 "parts", etc.

 

[aminnuto]

Well, the amount of the bonus will be different every month. I want to add the amount that I choose to bonus to a cell. Then, I want each of my 100 sales people to get portion of the bonus "parts" based on their Rank. So, I'm going to divide the bonus amongst all 100 but I want to reward the top sales people more than the bottom sales people.

Lets say the Top 3 people gets a fixed amount of 10%, 5% and 2.5% respectively. But then 4-100 get slightly less than the previous position. The 100th person would get the smallest portion of the AMOUNT. This will create an incentive for each person to get to a higher position so that they get a larger bonus.

You will need to be more detailed than that. How exactly is the amount to be determined? What is the logic?
There are many different ways it could be done.

For example, person 1 could receive half of the bonus, then person 2 could receive half of that, and person 3 could receive half of that, etc.
Or it could be down incrementally, i.e. person 1 receives 100 "parts", person 2 receives 99 "parts", person could receive 98 "parts", etc.

 

[Joe4]

Well, the amount of the bonus will be different every month. I want to add the amount that I choose to bonus to a cell. Then, I want each of my 100 sales people to get portion of the bonus "parts" based on their Rank. So, I'm going to divide the bonus amongst all 100 but I want to reward the top sales people more than the bottom sales people.

Lets say the Top 3 people gets a fixed amount of 10%, 5% and 2.5% respectively. But then 4-100 get slightly less than the previous position. The 100th person would get the smallest portion of the AMOUNT. This will create an incentive for each person to get to a higher position so that they get a larger bonus.

There are literally an unlimited ways in order to do this (any which way you can imagine).
You need to clearly define EXACTLY what the rule is, and then we may be able to help you write a program to do it. But you need to let us know what the logic is!
You defined exactly how to do the first three people (10%, then 5%, then 2.5%). Now you need to define the rest.

 

[aminnuto]

There are literally an unlimited ways in order to do this (any which way you can imagine).
You need to clearly define EXACTLY what the rule is, and then we may be able to help you write a program to do it. But you need to let us know what the logic is!
You defined exactly how to do the first three people (10%, then 5%, then 2.5%). Now you need to define the rest.

Right. Of the unlimited ways, I just need 1 way and then I'll tweak the variables. There is no specific fixed logic other than I want to create an incentive for people to get to the top of the list. So each position they move up gets them a little more of the Bonus amount (weighed) so that each increased rank gets the sales person a little more of the bonus amount up to 2%.

 

[Joe4]

Well, if we are trying to come up with a formula, there may not be an option to "tweak the variables", as different logic will require very different formulas (the formula structure will most likely be what is different, not the variables).

It is also important to know the following: is it always going to be the top 100, or might that number change?
And how exactly is the data presented on your sheet? Do you have a column for name and a column for ranking?

 

[aminnuto]

Well, if we are trying to come up with a formula, there may not be an option to "tweak the variables", as different logic will require very different formulas (the formula structure will most likely be what is different, not the variables).

It is also important to know the following: is it always going to be the top 100, or might that number change?
And how exactly is the data presented on your sheet? Do you have a column for name and a column for ranking?

We have thousands of sales people but I want to give a bonus to the top 100. So, we will produce the list of our top 100 sales people (sorted by sales that month). Lets make it easier so that only the top person gets a fixed percentage and everyone else gets less (percentage wise) of the previous position.

So, lets say I have a cell with an amount in it. Say 100.00

The top person gets 1% of the bonus amount. 2-100 split the remaining 99% so that the last person gets .01% (1 penny or something tiny but not 0)

 

[Joe4]

The top person gets 1% of the bonus amount. 2-100 split the remaining 99% so that the last person gets .01% (1 penny or something tiny but not 0)

That wouldn't work, because everyone could get 1% if there are 100 people (as 100 * 1% is 100%)! So the top person needs to get more than 1% if there are 100 people!

I am going to show you one way I came up with and you can tweak it however you want.
I am assuming:
- Top 100 ranked people get bonuses
- The top 3 get 10%, 5%, and 2.5% percent, as you originally requested.

I then create a column for "points". The formula in that column is the number of people to consider (100) plus 1 minus their ranking.
So the 4th places person would get 97 points (100+1-4), and the 5th places person would get 96 (100+1-5), all the way down to the last place person who gets 1 (100+1-100).
I then take each persons points, and divide by all the points, and multiply by the remaining percentage of 82.5% (100%-10%-5%-2.5%). And that is the percentage each person gets.

So the first ten records look like this:

and the last ten and totals look like this:

The formulas I used are:

In cell B4, copied down to cell B101:

=MAX(A$5:A$101)-A5+1

In cell C4, copied down to cell C101:

=B5/SUM(B$4:B$101)*(100%-SUM(C$2:C$4))

So that gives you one way that seems to meet your criteria.

 

[aminnuto]

That wouldn't work, because everyone could get 1% if there are 100 people (as 100 * 1% is 100%)! So the top person needs to get more than 1% if there are 100 people!

I am going to show you one way I came up with and you can tweak it however you want.
I am assuming:
- Top 100 ranked people get bonuses
- The top 3 get 10%, 5%, and 2.5% percent, as you originally requested.

I then create a column for "points". The formula in that column is the number of people to consider (100) plus 1 minus their ranking.
So the 4th places person would get 97 points (100+1-4), and the 5th places person would get 96 (100+1-5), all the way down to the last place person who gets 1 (100+1-100).
I then take each persons points, and divide by all the points, and multiply by the remaining percentage of 82.5% (100%-10%-5%-2.5%). And that is the percentage each person gets.

So the first ten records look like this:
View attachment 117789

and the last ten and totals look like this:
View attachment 117790

The formulas I used are:

In cell B4, copied down to cell B101:

=MAX(A$5:A$101)-A5+1

In cell C4, copied down to cell C101:

=B5/SUM(B$4:B$101)*(100%-SUM(C$2:C$4))

So that gives you one way that seems to meet your criteria.

Thats what I needed. Thank you.

 

[Joe4]

You are welcome.
Glad I was able to help!