Assigning a Final Rating based on a value range (scorecard)

**einnalem** · 07-05-2011, 03:28 PM

Hi,

I am creating a scorecard and I'm looking for a formula that will automatically assign a 'final rating' according to the points received. I'd like to use the 1-5 range, 5 as the highest with 3 as the passing rate which is equivalent to 70 points.

Possible Points: 100
Passing Points: 70
Range: 1-5 (5 is highest)
Equivalent of 70 points is 3 in Final Rating

Help me please.

Thanks,
E.

**Paul** · 07-05-2011, 03:33 PM

Hi Einnalem, welcome to the forum.

What are the other ranges for 1, 2, 4 and 5?

If 0-34 is 1, 35-69 is 2, 70-80 is 3, 81-90 is 4 and 91-100 is 5, you could use something like:

=LOOKUP(A1,{0,35,70,81,91},{1,2,3,4,5})

Where A1 holds the points you're trying to rate. Hope that helps!

**einnalem** · 07-14-2011, 04:07 PM

Thanks Paul, that's very helpful!

**einnalem** · 07-14-2011, 04:43 PM

I was able to use the formula you provided, however, for this particular one, I can't figure out how to do it:

Point Value / Grading Scale

10 if > = 100% delivered before deadline / quota
8 if 99.99% to 80%
6 if 79.99% to 60%
4 if 59.99% to 40%
0.5 if 39.99% and below

Please help again? Thanks!

**Paul** · 07-14-2011, 05:19 PM

Using the same logic

=LOOKUP(A1,{0,40,60,80,100},{0.5,4,6,8,10})

Broken down it is:

=LOOKUP( value_to_evaluate , { array_of_criteria } , { array_of_results } )

The array_of_criteria needs to be in ascending order. The first value in the array should be the lowest value possible. If your values can go to -100, set that as the low value.

Each value in the array_of_criteria represents the next point where the return value in the array_of_results should change. There is a 1:1 relationship between the array_of_criteria and the array_of_results.

{criteria_a , criteria_b , criteria_c } , { result_a , result_b , result_c }

If the value you're testing is greater than criteria_b but less than criteria_c, result_b will be returned.

Hopefully this makes sense!