On Fri, 20 May 2005 23:29:52 -0400, Ron Rosenfeld
wrote:
Set up a table with your equivalences:
Excellent 5
Good 4
Average 3
Fair 2
Poor 1
In an adjacent column to your ratings, enter the formula:
(assumes ratings start in A1, and that the table is NAME'd tbl, although you
could use absolute cell references instead)
=VLOOKUP(A1,tbl,2,FALSE)
Average the resultant numbers.
If you do not want to use an adjacent column and lookup table, you could use
the following formula:
=SUM(COUNTIF(A:A,"excellent")*5,COUNTIF(A:A,"Good ")*4,
COUNTIF(A:A,"Average")*3,COUNTIF(A:A,"Fair")*2,
COUNTIF(A:A,"Poor"))/COUNTA(A:A)
You may want to adjust the reference to column A.
As Leo pointed out, I had the equivalences backwards, but you should be able to
change them easily.
--ron
|