6 Replies Latest reply: Jan 18, 2013 11:54 AM by jcrowton
jcrowton Level 1 Level 1 (0 points)

I have a name column (A) and a test score column (B) on my first sheet. In the test score column, each student receives a score out of 100.

 

I want to seperate those students into 3 groups on another sheet. Group 1, 2, and 3 respectively. Group 1 would be students who received a 90 or above, Group 2, 80 and above, and Group 3, less than 80.

 

I want those lists to populate automatically by student name based on their score. The problem I am having using an if/then function is it returns blank cells in each group. I want it to index the list so that all students names are together without any blank cells. So if there are 10 students who recieved 90 or above their names populate the first 10 cells on the Group 1 column. I know excel can do this, but cannot get it to work in numbers.

 

Help!


MacBook Pro
  • 1. Re: Index help in Numbers! (teacher)
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    What order do you want the names to be in in the three category tables? Ordered by name, or score?

     

     

    Jerry

  • 2. Re: Index help in Numbers! (teacher)
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Here's a possible implementation:

    Screen Shot 2013-01-18 at 2.17.31 pm.png

    You have three calculated columns added to your main record to categorize the results. Data from the AllScores table are then pulled into separate class tables based on the calculations.

     

    AllScores::C2 is:

     

    =IF(B2>=90, "A", IF(B2>=80, "B", "C"))

     

    AllScores::D2 is:

     

    =COUNTIF(OFFSET($C$1, 0,0, ROW()), C)

     

    AllScores::E2 is:

     

    =C&D

     

    Select these three cells and Fill to the bottom of the table.

     

    A::A2 is:

     

    =IFERROR(INDEX(AllScores, MATCH("A"&ROW()-1, AllScores :: E, 0), 0), "")

     

    A::B2 is:

     

    =IFERROR(INDEX(AllScores, MATCH("A"&ROW()-1, AllScores :: E, 0), 2), "")

     

    Select these two cells and Fill to the bottom of the table.

     

    For tables B and C, substitute B or C where you see that letter in quotes in the formula.

     

    Is this what you had in mind?

     

    Jerry

  • 3. Re: Index help in Numbers! (teacher)
    jcrowton Level 1 Level 1 (0 points)

    Ordered by name.

     

    It would be really nice if on the category tables there was a spot for name and score, and then ordered by score.

     

    So Group 1 would have the students name with scores in descending order. For example Josh 98, Alyssa, 96, etc.....

     

    Hopefully that made sense.

  • 4. Re: Index help in Numbers! (teacher)
    jcrowton Level 1 Level 1 (0 points)

    That is perfect!! Thanks so much!! Is there a way to order the scores automatically by descending order in the groupings?

  • 5. Re: Index help in Numbers! (teacher)
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Sort the main table that way and the groupings will follow.

     

    Jerry

  • 6. Re: Index help in Numbers! (teacher)
    jcrowton Level 1 Level 1 (0 points)

    Thank you so much!! You just saved me hours of frustration and perhaps a few choice words... Thank you!