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. |
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 |
"Ron Rosenfeld" skrev i en meddelelse
... 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 |
One (short but complex) option is:
=SUM(IF(TRANSPOSE(A2:A100)=C2:C5,D2:D5)) 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! C2:D5 is the reference list: Text Rate Excellent 1 Good 2 Fair 3 Poor 4 |
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 ... 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. |
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 |
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 |
All times are GMT +1. The time now is 07:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com