Grade Rating

[Thomas Kuriakose]

Respected Sirs,

I am not able to work out the calculation for the attached rating sheet.

There are four grades - 1, 2, 3 & 4
There are two sections with 50% weightage
Each section has activities and these activities have weightage and we need to score the grades on these activities.

I have input the final result for achieved, but not able to figure out the equation to arrive at the score and achieved values.

Thank you so much,

with regards,
thomas

 

[Luke M]

I'm not sure where the 150 portion comes from in your denominator, but numerator would be:
=SUMPRODUCT(Scores,Percentages)
e.g. for Section 2:
=SUMPRODUCT(C6:C9,D6:D9)

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution on the achieved values. This is perfect.

The 150 is a fixed parameter.

But, I am not able to figure out the score values based on the rating also.

when the rating is 3.8 for activity "b" which has a weightage of 25% and 3.8 is close to 4 means excellent how is the score of 100 arrived at.

Thanks once again,

with regards,
thomas

 

[Luke M]

Looks like with your scale then, if you get a rating of 1, you would get no points? Formula in C6 then would be:
=(B6-1)/3*150

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution.

Thanks,

with regards,
thomas