+ Reply to Thread
Results 1 to 8 of 8

Assign equivalent numerical values to characters and calculate average

  1. #1
    Registered User
    Join Date
    04-08-2010
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    9

    Assign equivalent numerical values to characters and calculate average

    Hi,

    I need to calculate student average of large dataset using excel.
    It includes numerical equivalent for several characters such as A+ = 4.25, A = 4, A- =3.75, B+ = 3.25 and so on.

    Information on the thread

    http://www.excelforum.com/excel-work...d-average.html

    really helpful to me to get an idea.

    But the above method is not working for it. May be that includes + and - signs with character.

    is there any solution for that.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Assign equivalent numerical values to characters and calculate average

    Seems you only need to adapt the formula to include the extra characters.

    Adapting the formula from the post to which you linked:

    =AVERAGE(IF(ISTEXT(A1:E1),LOOKUP(A1:E1,{"A+","A","A-","B+","B","B-"},{4.25,4,3.35,3,2.75})))

    This is an array formula and must be committed using Ctrl+Shift+Enter.

    Continue developing the remainder of the formula for the full range of grades.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Registered User
    Join Date
    04-08-2010
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    9

    Re: Assign equivalent numerical values to characters and calculate average

    Lot of thanks on the given formula. I already tried this method. But it does not work due to the additional + and - marks with characters.

    this method works only when
    =AVERAGE(IF(ISTEXT(A1:E1),LOOKUP(A1:E1,{"A","B"},{4,3}))) and got answers correctly.

    I want to improve this for
    =AVERAGE(IF(ISTEXT(A1:E1),LOOKUP(A1:E1,{"A+","A","A-","B+","B","B-"},{4.25,4,3.35,3,2.75})))

    Need suggestions on this...

    thanks

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Assign equivalent numerical values to characters and calculate average

    LOOKUP requires the lookup array to be in ascending order.

    =AVERAGE(LOOKUP(A1:D1, {"A";"A-";"A+";"B";"B-";"B+";"C";"C-";"C+";"D";"D-";"D+"}, {4;3.75;4.25;3;2.75;3.25;2;1.75;2.25;1;0.75;1.25}) )
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    04-08-2010
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    9

    Re: Assign equivalent numerical values to characters and calculate average

    wow... This method perfectly works on my problem

    thank so much for this help... Finally it becomes to a few second calculation..
    Thank you so much again

  6. #6
    Registered User
    Join Date
    04-08-2010
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    9

    Re: Assign equivalent numerical values to characters and calculate average

    how can improve the code to calculate average of number of cells when there are terms include such as AB (for student absent in exam) in between those cells.

    need to exclude those cells and calc average from other data...
    Try with AVERAGEIF as follows

    =AVERAGEIF(LOOKUP(A1:D1, {"A";"A-";"A+";"B";"B-";"B+";"C";"C-";"C+";"D";"D-";"D+"}, {4;3.75;4.25;3;2.75;3.25;2;1.75;2.25;1;0.75;1.25}),"<>*(AB)" )

    but it doesn't work ...Is there any method to overcome this ?

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Assign equivalent numerical values to characters and calculate average

    =average(lookup(a1:d1, {"a";"a-";"a+";"ab";"b";"b-";"b+";"c";"c-";"c+";"d";"d-";"d+"}, {4;3.75;4.25;false;3;2.75;3.25;2;1.75;2.25;1;0.75;1.25}) )

  8. #8
    Registered User
    Join Date
    04-08-2010
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    9

    Re: Assign equivalent numerical values to characters and calculate average

    Well it works nice & smooth... Thanx a lot

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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