+ Reply to Thread
Results 1 to 7 of 7

Excellent=1, Good=2, etc.

  1. #1
    Kmarie
    Guest

    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. #2
    Ron Rosenfeld
    Guest

    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

    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. #3
    Leo Heuser
    Guest

    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





  4. #4
    Ola
    Guest

    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

  5. #5
    Arvi Laanemets
    Guest

    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.




  6. #6
    Ron Rosenfeld
    Guest

    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

  7. #7
    Ron Rosenfeld
    Guest

    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
    >
    >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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1