# Excellent=1, Good=2, etc.

1. ## 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.  Register To Reply

2. ## Re: Excellent=1, Good=2, etc.

On Fri, 20 May 2005 20:10:02 -0700, Kmarie <Kmarie@discussions.microsoft.com>
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

(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  Register To Reply

3. ## Re: Excellent=1, Good=2, etc.

"Ron Rosenfeld" <ronrosenfeld@nospam.org> skrev i en meddelelse
news:pdat8117ssliuehkti1ju9llh31j13n85k@4ax.com...

>
> 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","Fair","Poor"})*{5,4,3,2,1})/COUNTA(A:A)

Maybe even

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

:-)

LeoH  Register To Reply

4. ## RE: Excellent=1, Good=2, etc.

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  Register To Reply

5. ## Re: Excellent=1, Good=2, etc.

Hi

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

Arvi Laanemets

"Kmarie" <Kmarie@discussions.microsoft.com> wrote in message
news:D4ED1F81-C271-4CD7-A8EE-95930156C84B@microsoft.com...
> 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.  Register To Reply

6. ## Re: Excellent=1, Good=2, etc.

On Sat, 21 May 2005 09:17:58 +0200, "Leo Heuser" <leo.heuser@NOSPAMadslhome.dk>
wrote:

>
>Or shorter
>
>=SUM(COUNTIF(A:A,{"Excellent","Good","Average","Fair","Poor"})*{5,4,3,2,1})/COUNTA(A:A)
>
>Maybe even
>
>=SUM(COUNTIF(A:A,{"Excellent","Good","Average","Fair","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  Register To Reply

7. ## Re: Excellent=1, Good=2, etc.

On Fri, 20 May 2005 23:29:52 -0400, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>Set up a table with your equivalences:
>
>Excellent 5
>Good 4
>Average 3
>Fair 2
>Poor 1
>
>
>(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  Register To Reply