Excel formula to build scorecard. Decimal values of a scoring grid.

[sunnynayak]

I have designed a scorecard.
FY 2013 Goals
Parameter Weight 5 4 3 2 1
VOC 25% >=4.91 4.81-4.9 4.7-4.8 4.61-4.69 <=4.60
Case Closures 25% 7 6 5 4 3
ASA Critical 5% 100% 98% 95% 93% 90%

I can use the IF function like =IF(G3>=7,5,IF(G3>=6,4,IF(G3>=5,3,IF(G3>=4,2,1)))) to get the rating of 1 to 5. This is working well.

The help i need is to get the decimal values.
For E.g. if Case Closure is 5.5 the rating for that parameter should be a value between rating 4 and 3 and not default to 5

Hope i have explained what i am looking for.

[Melvinrobb]

would 5.5 case closures result in a rating of 3.5?

[sunnynayak]

would 5.5 case closures result in a rating of 3.5?

Yes. It is however tricky as the case closure can be a weird number as 5.76 etc.

[Melvinrobb]

For this specific example, you could just use

Please Login or Register  to view this content.

Obviously this won't work if you change the ratings, but...

You can also add this onto the end of your formula:

Please Login or Register  to view this content.

I suggest using the INDEX and match function for this:
Assuming your ratings are in b2:f2, and your Case closures are in b4:f4, the following would work
=INDEX($B$2:$F$2,1,MATCH(A7,B4:F4,-1))+(ROUND(g3,0)-g3)

[sunnynayak]

Thank you for the quick response.
I tried the Roundup and INDEX and MATCH method.

I see an issue there.

When i enter the closure as 5.80 The score shows 4.20 and when the closure is 5.50 the score shows 4.50

No clue what is wrong.

[Melvinrobb]

the formula i gave is incorrect, in a couple of places. Sorry about that.

First of all, in order for INDEX/MATCH to work, you need to list your rankings so 1-5 is listed left to right. That is because that is how the formula works. It looks in the first cell and sees if the value in g3 is lower than 7 and determines that since g3=5.8, it is lower than 7, and proceeds to look at the next cell in the row). when it gets to the cell value of 5, it recognizes that 5.8 is NOT lower than 5, so it stops on that column, and displays the associated value in the ranking row, in this 3. I also changed the round function, as what I gave you was just plain wrong.

Please Login or Register  to view this content.

[sunnynayak]

Thank this is working for closures but i am stuck for other parameters.


Rating 1 2 3 4 5

Quality 85% 87% 90% 92% 95%

How can i calculate this and other parameters.

[Melvinrobb]

Is this scenario, if the percentage is 88.5%, would you want a rating of 2.5 to appear, or 2?

[sunnynayak]

I would want the rating to appear as 2.5.

[Melvinrobb]

The attached should work. The formula now can work if the value is a number, of a percentage.

[sunnynayak]

Great. Thank you.