+ Reply to Thread
Results 1 to 3 of 3

Pulling test score based on date, not highest score.

  1. #1
    Registered User
    Join Date
    06-22-2009
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    38

    Pulling test score based on date, not highest score.

    What I'm trying to do is have a formula pull scores based on the highest overall test score. I have a workbook which might better explain it, but I'll try anyway.

    I have a student who took the SATs twice, once in May & another in October. His overall SAT score was better in the October one and I would like a formula to pull only the scores from that October SAT Test. I have the scores split into the sections of the test:

    Math
    Critical Thinking
    Writing
    Essay

    The formula I'm using right now only seems to pull the highest section score, but I just want it for the date with the highest overall score. The student did better in Writing on the March test, but I still want the October test score to show instead.

    This is the formula I am currently using:

    {=IF(ISNA(INDEX(Paste!$J$2:$J$10000,MATCH(C$1&$B146, Paste!$A$2:$A$10000&Paste!$G$2:$G$10000,0))), 0, (INDEX(Paste!$J$2:$J$10000,MATCH(C$1&$B146, Paste!$A$2:$A$10000&Paste!$G$2:$G$10000,0))))}

    I hope that kind of makes sense. I've been having the hardest time trying to get a formula for this.

    Any help would be greatly appreciated. Thanks!
    Attached Files Attached Files
    Last edited by PowerSchoolDude; 11-30-2009 at 06:35 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,529

    Re: Pulling test score based on date, not highest score.

    I would strongly suggest you store a ranking of sorts on the Paste sheet.

    Given you're using XL2007 you can make use of the SUMIFS & COUNTIFS functions in pref. to Arrays given they are significantly more efficient.

    Please Login or Register  to view this content.
    With the above in place you can then collate your info on 'By Wm' sheet such that:

    Please Login or Register  to view this content.
    by my reckoning the results should be 800, 800, 600, 10 rather than 630,550,800,9 ... your prior Array would only ever return the results for the first first test that met requirements (ie name & code) the scores themselves were never used as any basis for selection (ie MAX was never being accounted for).

    The use of SUMIFS will be more efficient than the Array and also removes need for error handler (0 will be result should a combination not exist).

  3. #3
    Registered User
    Join Date
    06-22-2009
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Pulling test score based on date, not highest score.

    Wow... thank you sooooooooooooooooooooo much! I was having such a hard time and this solved everything!

    Thank you so much for helping me out. You rock!

+ 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