Rating

**sentinela** · 03-23-2009, 08:48 AM

Hello everyone!
I am trying to do some rating in excel.
This is what I am trying to do.
1- excellent
2- good
3- average
4- fair
5- poor
I have the in a validation all of the words as option; but I want once they pick the words, in the cell beside will come up the number instead, and that will be the rating.
This is my coding

Please Login or Register  to view this content.

Is there a better way to do that? Could anyone else give me some ideas?

Thanks!

**shg** · 03-23-2009, 10:15 AM

In C2 and copy down, =IF(B2="", "", MATCH(B2, {"excellent","good","average","fair","poor"}, 0) )

**sentinela** · 03-23-2009, 02:37 PM

shg

Thanks for the reply. For some reason the formula that you gave me is not working. This is how I put.

=IF(B2="POOR", "FAIR", "AVERAGE", "GOOD", "EXCELLENT", MATCH(B2,{"5","4", "3", "2", "1"},0))

Once I click enter, this is what comes up...

You've entered too many arguments for this function.

I don't quite get what I am doing wrong. Any ideas?

Thanks!

**martindwilson** · 03-23-2009, 02:42 PM

well the reason is that you havent followed shg's instructions!
just use that formula as stated

**sentinela** · 03-23-2009, 02:50 PM

Thank you very much martindwilson.

It worked. But could you give some more explanation on how that works. I just want to understand what happened.

Thanks!

**martindwilson** · 03-23-2009, 02:58 PM

=IF(B2="", "", if b2 is blank return blank if not then
MATCH(B2, {"excellent","good","average","fair","poor"}, 0) )
which takes the text in b2 and matches it against the array
{"excellent","good","average","fair","poor"}
and returns its position in the array so poor being in 5th position returns 5
the ,0) at the end of the match function says "look for exact match"