Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 21st 05, 04:10 AM
Kmarie
 
Posts: n/a
Default Excellent=1, Good=2, etc.

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.

  #2   Report Post  
Old May 21st 05, 04:29 AM
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
  #3   Report Post  
Old May 21st 05, 08:17 AM
Leo Heuser
 
Posts: n/a
Default

"Ron Rosenfeld" skrev i en meddelelse
news

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.


Hi Ron

Or shorter

=SUM(COUNTIF(A:A,{"Excellent","Good","Average","Fa ir","Poor"})*{5,4,3,2,1})/COUNTA(A:A)

Maybe even

=SUM(COUNTIF(A:A,{"Excellent","Good","Average","Fa ir","Poor"})*{1,2,3,4,5})/COUNTA(A:A)

:-)

LeoH




  #4   Report Post  
Old May 21st 05, 11:23 AM
Ola
 
Posts: n/a
Default

One (short but complex) option is:
=SUM(IF(TRANSPOSE(A2:A100)=C2:C5,D25))

Hope it helped
Ola Sandström


Note:
This is an Array formula (=it does several calculation in one cell)
All array formulas must be confirmed by holding down Ctrl and Shift then hit
Enter.
Just hit - the normal - Enter will not work -- #VALUE!

C25 is the reference list:
Text Rate
Excellent 1
Good 2
Fair 3
Poor 4
  #5   Report Post  
Old May 21st 05, 12:34 PM
Arvi Laanemets
 
Posts: n/a
Default

Hi

Entered as array formula (with Ctrl+Shift+Enter), assuming your ratings are
in range A2:A100:
=AVERAGE(MATCH(A2:A100,{"Excellent";"Good";"Averag e";"Fair";"Poor"},0))

Arvi Laanemets


"Kmarie" wrote in message
news
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.





  #6   Report Post  
Old May 21st 05, 01:26 PM
Ron Rosenfeld
 
Posts: n/a
Default

On Sat, 21 May 2005 09:17:58 +0200, "Leo Heuser"
wrote:


Or shorter

=SUM(COUNTIF(A:A,{"Excellent","Good","Average","F air","Poor"})*{5,4,3,2,1})/COUNTA(A:A)

Maybe even

=SUM(COUNTIF(A:A,{"Excellent","Good","Average","F air","Poor"})*{1,2,3,4,5})/COUNTA(A:A)



I like that. And your second answer even gives the result expected by the
OP!!!!


--ron
  #7   Report Post  
Old May 21st 05, 01:27 PM
Ron Rosenfeld
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 04:14 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright ©2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017