On Fri, 20 May 2005 20:10:02 -0700, Kmarie
wrote:
Ok, I want to average the ratings for a particular product. Here are the
ratings:
Excellent
Good
Excellent
Fair
Good
Good
Poor
If Excellent=1, Good=2, Average=3, Fair=4, Poor=5.... then what's the
average? I'm trying to find a formula that will calculate this.
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.
--ron
|