Scores Distribution

[shantraj.antin@gmail.com]

Hi Sir,


I have the 5 people working on set of volumes.I Have the score of 30 which is standard. This need to be distributed among the people worked on the volumes. I need to get what is the score of Person A out of 30 and similarly for all the people. Can you please help me with the formula to get this.


Person Volume Score

A 150 ?

B 75 ?

C 125 ?

D 200 ?

E 100 ?

 

[Hui]

Shan

Assuming the Scores are in B1:B5

use: =30*B1/SUM($B$1:$B$5)

Copy down for the other people

[pre]

a	150	 6.92
b	75	 3.46
c	125	 5.77
d	200	 9.23
e	100	 4.62
650	 30.00

[/pre]

 

[shantraj.antin@gmail.com]

Thank you Hui,


How about in case of one more parameter to consider like Defects, again score is 30 to be distributed among all the 5 people.


Person Volume Defect Score

A 150 1 ?

B 75 0 ?

C 125 2 ?

D 200 5 ?

E 100 0 ?

 

[shantraj.antin@gmail.com]

In addition to my previous problem, the person having less defects should have more score, also considering the # of volumes.

 

[shantraj.antin@gmail.com]

Hi Sir,


I got solution for this situation =((B1-C1)/B1*100)*30/100


Thank you again for your support