Skip navigation

Help with a lookup function (Vlookup, etc) to find grades

301 Views 7 Replies Latest reply: Apr 18, 2013 11:48 AM by jturk1000 RSS
jturk1000 Calculating status...
Currently Being Moderated
Apr 15, 2013 10:48 AM

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

  • Wayne Contello Level 6 Level 6 (12,650 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.

  • Barry Level 7 Level 7 (29,095 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

  • Barry Level 7 Level 7 (29,095 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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.