Overall rating based on an alphabetical score

**OlSchool** · 06-23-2011, 05:22 AM

Hi Guys

I'm trying to come up with a document for our consultants to show them their overall ratings for the last 6 months.

They have 3 tasks to which targets are given, if they achieve, over achieve, did not meet targets they get a rating of A,B,C or D.

I need to know how to calculate their overall rating for the 3 scenarios, so if they scored an A, C and D, what is their overall rating?

I have attached a workbook.

Thanks!

**Roger Govier** · 06-23-2011, 06:02 AM

Hi

Well it is only you who can decide the overall rating.
The attached workbook is a suggestion of how it might be calculated, with some extra lookup ranges in columns A through V

You will need to set the values you want to allocate in Final

**OlSchool** · 06-23-2011, 08:27 AM

Originally Posted by Roger Govier

Hi

Well it is only you who can decide the overall rating.
The attached workbook is a suggestion of how it might be calculated, with some extra lookup ranges in columns A through V

You will need to set the values you want to allocate in Final

Thank you Roger!

The solution you gave worked perfect!

However, i added additional measurements and now i'm getting a N/A error.

I have attached the workbook with my changes

**vishrocks** · 06-23-2011, 08:43 AM

Originally Posted by OlSchool

Thank you Roger!

The solution you gave worked perfect!

However, i added additional measurements and now i'm getting a N/A error.

I have attached the workbook with my changes

Hi

When you have added up all the grades ie. A+A+D+A i.e 5+5+1+5=16,according to your overall Table.But the digit 16 is not available in the Final table where u have assinded the final grades.to get a better idea just read more about the vlookup and Hlookup funtions,they are quite handy

VISH

**OlSchool** · 06-23-2011, 09:45 AM

Originally Posted by vishrocks

Hi

When you have added up all the grades ie. A+A+D+A i.e 5+5+1+5=16,according to your overall Table.But the digit 16 is not available in the Final table where u have assinded the final grades.to get a better idea just read more about the vlookup and Hlookup funtions,they are quite handy

VISH

Thanks vish, i will do so.

How does the table "Final Table" work? i.e. how is it calculated? I know that the "Overall" table has values assigned to them and that 2 B's = 6 and a 6 on the final table = C

**Roger Govier** · 06-23-2011, 10:10 AM

Originally Posted by OlSchool

Thank you Roger!
The solution you gave worked perfect!
However, i added additional measurements and now i'm getting a N/A error.

Hi

Your potential maximum is now 20 ( 4 gradings x 5 points) but the range for final still only goes to 15.

There are other potential errors as well, which I have eliminated with some IF tests and an IFERROR test.
See attached book