View Single Post
  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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