Contibution to High

0

In column A I have the name of employees and In column B I have the target value that they achieved. Now I want that the employee who achieved the highest target, other employees contributes their value to him. Like if any employee who achieved 51% target of Highest target employee value then he contributes Rs 100 and if other employees who achieved 26 to less than 51% of highest target value employee then they contributes Rs 150 and for less than or equal to 25 % contributes Rs 200.

My list is like below.

Emp Name Tgt Acd    Highest Emp     Final Result
   A                  20         70 is highest    20 is 28 % of 70 ..now A pay to C 150 Rs
   B                  10                                 10 is 14% of 70..now B pay to C 200 Rs
   C                  70   
   D                  45                                 45 is 64% of 70..now D pay to C Rs 100
   E                  50   

Answer
Discuss

Answers

0

An amusing scheme :-)
The formula below almost does what you want.

=INDEX({200,150,100,0},MATCH($B2/MAX(Achieved)*100,{0,25,50,100},1))

It's "almost" because I thought you should be more generous. I made it 50% instead of 51% and 25% instead of 26%. Note that the calculation of the percentage is very exact. 49.99999% is less than 50%. You can modify my formula, however. It's the array {0,25,50,100} which sets the limits. 0 is required to exclude negative results, 100 is needed to award 0 Rps to the winner. 50 and 25 are the values you can change. You can use 50.00001 and 25.00001 to penalise 50% and 25%. If you set the limit at 51% then 50.99999% will pay the higher fee.

The other array {200,150,100,0} contains the amounts to be given to the winner. You can change these amounts when inflation hits. The final zero is for the one who has 100%, which is the winner himself. There is no provision for a tie. The contribution would be calculated correctly for all but the two winners who both get nothing. Perhaps they will have to share the spoils. However, the highest is calculated very exactly. Rps 0.01 is all it takes to crown a king.

To make the formula work in your worksheet enter the achieved targets in column B. I assigned a name to the range comprising all achieved targets. In my worksheet that was $B$2:$B$6 and I assigned the name "Achieved". Using a name makes the formula easier to read and manage, but you can replace "Achieved" in the formula with $B$2:$B$6.

Paste the formula in row 2 and copy down. If your "Achieved" range is not in column B, change the reference to $B2 in the formula to wherever your achieved targets are.

Discuss

Discussion

As suggested by you I have set my B column heading as Achieved and paste this formulae in C2 but its display  #Name?
Mannu (rep: 2) Apr 7, '18 at 6:18 am
Thanks Dear. its wroking..Actually i forgot to replace Achieved with $B$2:$B$6
Mannu (rep: 2) Apr 7, '18 at 7:50 am
Since it's working, please mark the answer as "accepted". However, I urge you to use a named range instead of the range address. In that way, when the number of achievers changes in future, you just modify the named range instead of replacing all the formulas.
The quickest way to name a range is to select it and type the name in the Name Bar (to the left of the Formula Bar, just above the A1 cell). To modify it select "Name Manager" from the ribbon's Formula tab.
Variatus (rep: 4262) Apr 7, '18 at 9:11 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login