Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VLOOKUP troubles!!!!!!

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have four columns of data that are grades.There is one grade in each column.
    (i.e the grades can consist of the following
    F - 1.5
    PC - 3
    P - 4
    C - 5
    D - 6
    HD - 7)
    I want a total column, that averages the 4 grades. (i.e if in one row I have P,C,D,C the answer I should get is 5. For some reason only half my lookup table works, I cannot get all the grades to convert to numerical format.
    Thanks for help.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bass
    I would like to help, but I do not understand how your "lookup" table is setup exactly...
    Please post a few more details for us slow learners, or if you'd like, E-Mail a sample with an example of expected results to TsTom@Hotmail.com

    Have you checked into HLookUp?

    Thanks,
    Tom

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom, thanks for helping out, I've email you the sheet, hop its not too confusing.

    Thanks

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-22 22:45, bass00 wrote:
    I have four columns of data that are grades.There is one grade in each column.
    (i.e the grades can consist of the following
    F - 1.5
    PC - 3
    P - 4
    C - 5
    D - 6
    HD - 7)
    I want a total column, that averages the 4 grades. (i.e if in one row I have P,C,D,C the answer I should get is 5. For some reason only half my lookup table works, I cannot get all the grades to convert to numerical format.
    Thanks for help.
    I take it that what follows is your 2-column table of conversion:

    {"F",1.5;
    "PC",3;
    "P",4;
    "C",5;
    "D",6;
    "HD",7}

    Select all the cells containing the grade symbols, go to the Name Box on the Formula Bar, type GSYMS, and hit enter.

    Select all of the cells containing symbols and numbers, go to the Name Box, type GRADES, and hit enter.

    Lets say that A2:D2 houses the following grades:

    {"P","C","D","C"}

    In F2 enter:

    =SUMPRODUCT(N(OFFSET(GRADES,MATCH(A2:D2,GSYMS,0)-1,1,1,1)))/COUNTA(A2:D2)

    which will compute the desired average.

    Note. I'd suggest using numerical values instead of grades if possible, because a computation like the one above is rather costly.

    Addendum: If you want to avoid getting #DIV/0! when a range has no grades at all, use:

    =SUMPRODUCT(N(OFFSET(GRADES,MATCH(A2:D2,GSYMS,0)-1,1,1,1)))/MAX(1,COUNTA(A2:D2))

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-22 23:56 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My system date is all screwed up...
    I do not know if you recieved my replies or not...
    This formula worked on the sheet you sent me.
    Place it in Cell g4 on your Summary sheet and copy down:

    =AVERAGE(VLOOKUP(C4,J$4:K$9,2,FALSE),VLOOKUP(D4,J$4:K$9,2,FALSE),VLOOKUP(E4,
    J$4:K$9,2,FALSE),VLOOKUP(F4,J$4:K$9,2,FALSE))


    Thanks Aladin...
    He had the right formula for the job, but the values in his table were not in ascending order. I know next to nothing about Excel functions but was able to figure out the need for the range_lookup = FALSE.
    That was the problem
    Tom

    [ This Message was edited by: TsTom on 2002-04-23 00:22 ]

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-23 00:17, TsTom wrote:
    My system date is all screwed up...
    I do not know if you recieved my replies or not...
    This formula worked on the sheet you sent me.
    Place it in Cell g4 on your Summary sheet and copy down:

    =AVERAGE(VLOOKUP(C4,J$4:K$9,2,FALSE),VLOOKUP(D4,J$4:K$9,2,FALSE),VLOOKUP(E4,
    J$4:K$9,2,FALSE),VLOOKUP(F4,J$4:K$9,2,FALSE))


    Thanks Aladin...
    He had the right formula for the job, but the values in his table were not in ascending order. I know next to nothing about Excel functions but was able to figure out the need for the range_lookup = FALSE.
    That was the problem
    Tom

    [ This Message was edited by: TsTom on 2002-04-23 00:22 ]
    That formula becomes a hell of a job if you have a lot of grades in a row and, I think, even costly if it must be copied down to many rows.

    Aladin

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You would know better than I...
    I was just taking what was already there and making it work...
    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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