7 Replies Latest reply: Apr 18, 2013 11:48 AM by jturk1000
jturk1000 Level 1 Level 1 (0 points)

I have a spreadsheet that is the compilation of student grades.  It is similar to:

 

Header row is first

Name     Exam1     Exam2     Exam3     Total     %       Instructor

bob          10               10              5          25     83            A

jeff           15                5               9         29     97            B

 

and so on...

 

I compile grades for several instructors (A through...)

 

I would like to create sheets within the master that is a compilation of ONLY instructor A's grades (and B's, etc).

 

For example:

 

Sheet for instructor A, all fields would populate with grades that have "A" listed as instructor.  Note, only Total and % are revealed.

 

Header row is first

Name           Total     %

bob                  25     83

 

These three fields can probably be populated using a Vlookup, but I'm not smart enough to figure it out - can you help?

 

I hope I was clear enough in describing my task.

 

- Jeff

  • 1. Re: Help with a lookup function (Vlookup, etc) to find grades
    Wayne Contello Level 6 Level 6 (13,615 points)

    Jeff,

     

    Is there a reason not to segment the students by instructor from the beginning?  If there is not then I would sort by instructor then duplicate the sheet (or table) and remove the non-A information in one sheet, while removing non-B information from the next, and so on.

  • 2. Re: Help with a lookup function (Vlookup, etc) to find grades
    Barry Level 7 Level 7 (29,180 points)

    Hi Jeff,

     

    Will tihs work?

    Picture 1.png

     

    The index is created by this formula, entered in H2 and filled down:

     

    =G&" "&COUNTIF($G$2:G2,G2)

     

    The smaller table contains two versions of the formula below, entered in the indicated cell, and filled down (and right):

     

    A2: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $H,0)-1,0),"")

    B2: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $H,0)-1,COLUMN()+2),"")

     

    Two versions are necessary as the columns from which values are to be transferred are not contiguous.

    The first formula is the single column version, used to transfer the names from column A. It is filled down to the bottom row of the small table.

     

    The second is used to transfer the total from column E and percent from column F. Because these columns are adjacent to each other, the fixed value in the first formula ( 0 ) can be replaced with a formula ( COLUMN()+2 ) to allow filling this formula right into column C as well as down to the bottom row of the small table.

     

    The second small table is a duplicate of the first. The second instructor name has been entered in cell A1.

     

    Dexscriptions, syntax, and examples for each of th functions used can be found in the iWork Formulas and Functions User Guide, which may be downloaded via the Help menu in Numbers '09.

     

    Regards,

    Barry

  • 3. Re: Help with a lookup function (Vlookup, etc) to find grades
    jturk1000 Level 1 Level 1 (0 points)

    Wayne: I have the students grouped by which course section they are enrolled in (there are 9 sections).  I didn't let on that these were laboratory courses that have students from all different instructors.  But the lab course does not carry credit - so it is important for me to lump the students together by instructor so he or she can add this lab grade to their lecture component.  OTHERWISE, what you stated would be a nice fix

  • 4. Re: Help with a lookup function (Vlookup, etc) to find grades
    jturk1000 Level 1 Level 1 (0 points)

    Thanks, Barry - what you have here looks great - now to see if I can implement it myself

  • 5. Re: Help with a lookup function (Vlookup, etc) to find grades
    jturk1000 Level 1 Level 1 (0 points)

    Hmmm...  I can't seem to apply this to my spreadsheet.  Let me post a portion of my actual database table (titled Table 1). Rows 1 and 2 are header rows, names (and grades) begin in row 3.  I have the index successfully in place (right-most column), but when I try to modify what you've placed here, I get only empty cells in the two small tables.

     

    Name

    Caffeine

    MP & Distillation

    Fractional Distillation

    Acid/Base Extraction

    Prep GC

    TLC & Column

    Multi-Step

    Photochem

    Dehydration

    1-Octanol

    NADH

    Total

    %

    PTS(50)

    Instructor

     

     

    10

    10

    10

    10

    10

    10

    10

    10

    10

    10

     

    110

     

     

     

     

    Dan

    9

    10

    10

    10

    10

    10

    9

    9

    10

    10

     

    97

    97%

    49

    A

    A 1

    Jacob

    10

    10

    10

    10

    9

    9

    9

    10

    10

    10

     

    97

    97%

    49

    B

    B 1

    Perry

    9

    10

    10

    9

    10

    8

    9

    9

    9

    9

     

    92

    92%

    46

    A

    A 2

    Robert

    8

    8

    10

    9

    10

    10

    8

    9

    9

    10

     

    91

    91%

    46

    A

    A 3

    Laura

    10

    9

    9

    10

    9

    9

    9

    9

    10

    10

     

    94

    94%

    47

    B

    B 2

  • 6. Re: Help with a lookup function (Vlookup, etc) to find grades
    Barry Level 7 Level 7 (29,180 points)

    Hi j,

     

    When trying to troubleshoot a formula using an error trap, start by stripping the IFERROR part from the formula, so that you can see what error the rest of the formula is returning.

     

    In your table, the index column is column Q, not H. Make sure you have changes that in both formulas.

    Your total column is M, not column E. This is reported in column 2 of the smaller tabllem so the column offset needs to be changes from COLUMN()+2 to COLUMN(()+10

     

    Here are the two formulas from the smaller table. Original first, revised version immediately below the original. Changes in bold.


    A2 old: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $H,0)-1,0),"")

    A2new: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $Q,0)-1,0),"")

     

    B2 old: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $H,0)-1,COLUMN()+2),"")

    B2new: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $Q,0)-1,COLUMN()+10),"")

     

    New sample tables:

    Picture 1.png

    Regards,

    Barry

  • 7. Re: Help with a lookup function (Vlookup, etc) to find grades
    jturk1000 Level 1 Level 1 (0 points)

    Awesome - thanks - it works great!