+ Reply to Thread
Results 1 to 11 of 11

Need to enter test scores and return suggestions based on those scores

  1. #1
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    14

    Need to enter test scores and return suggestions based on those scores

    I am happy to google 'how to' once I know what to google!

    I would like to enter in a list of secondary schools and the scores that are needed for entry into those schools. I then want to enter in a list of children with their scores and have excel return a list of schools for each child: a specific number of 'stretch' schools, a specific number of 'comfortable' schools and a specific number of 'back up' schools. I would like this list to be conditionally formatted for each child so that their stretch schools are coloured yellow and their back ups are coloured light purple.

    So far I have three sheets: one with the schools and the entry scores; one with the pupils names and their scores (and the number of each type of school I want returned); and one with the output.

    I have instructed Excel to copy the names from the INPUT sheet and put them in the OUTPUT sheet, but that's the only thing I know how to do. I know how to use vlookup and also how to conditionally format cells.

    Please could someone tell me which formulae I need to use. Again, I'm happy to do the googling to find out how to use them.

    (I think I've attached a screenshot of the INPUT sheet.)

    Many thanks.
    Attached Images Attached Images
    Last edited by deborahlane; 09-16-2020 at 11:21 AM. Reason: ETA: on a Mac

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,721

    Re: Need to enter test scores and return suggestions based on those scores

    Back up schools would be those where the student's scores exceed the schools' required scores by a large margin.
    Comfortable schools would be those where the student's scores exceed the schools' required scores by a small margin.
    Stretch schools would be those where the student's scores do not the schools' required scores.

    The +/- margins are what you would need to define. Depending on the number of schools in your data set, it is possible that many schools would fit each criteria, so you would need to either use random choices or have other criteria, like school type. No use sending an engineer student to an art school....

    It would be better to share a sample of your data set, with one or more example students, and sample returns that you would want for the student(s). You can attach a sample set in a workbook use the "Go Advanced" / "Manage attachments" functions.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Need to enter test scores and return suggestions based on those scores

    Hi, Bernie,

    Back up schools - large margin (15pts, perhaps); comfortable (5-10pts); stretch (no more than 5pts below)

    I agree that I will need other criteria. I would like to know what formulae I could use for that.

    I have attached a dummy workbook. The OUTPUT page needs reorganising, as it will only work with that specified number of schools in its current format but will become unwieldy if they're just listed all the way up to column CZ. Perhaps something with a bit more space under each name.

    I really appreciate you taking a look at this for me. As I said, I'm happy to do the legwork and google *how* to do it once I know what I'm actually going to use!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Need to enter test scores and return suggestions based on those scores

    Hi, Bernie,

    Just wondering if you had a chance to take a look. Not to worry if not.

    Thank you.
    Deborah

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    Re: Need to enter test scores and return suggestions based on those scores

    Bernie asked that you provide some sample returns (output).
    Please fill in cells A2:A6 on the Output sheet and give us the accompanying rational so that we can see if our proposed formulas/code accomplish your goal.
    It seems as if we would at least need to know Student A's score and gender in order to know which Schools to place in cells A2:A6.
    I also feel that it would be easier to list the schools in a row rather than in a column if that is a possibility.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Need to enter test scores and return suggestions based on those scores

    Thank you. That's really useful. I have tried to do as you suggested (and have realised it is not terribly straightforward!) and have attached a second attempt. I am very grateful for any help or advice that you can give.

    Deborah
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    Re: Need to enter test scores and return suggestions based on those scores

    I have put together an illustration of what I meant by "list schools in rows"
    The formula so far only addresses girls that want to attend single gender schools. I will be happy to add boys that want to include single gender schools and all students that want to attend mixed gender schools if the "rows" format is acceptable.
    I feel that attempting to put interests (sports, art etc.) into the formula is going to be pretty restrictive, but have included that as a condition so that you can see what the results might look like. We may be able to use conditional formatting to indicate schools that specialize in either the 1st or 2nd interest of the student, rather than including the interests in the formula.
    The formula used for backup school is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formulas for comfortable and stretch are similar.
    Let us know if you have any questions.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Need to enter test scores and return suggestions based on those scores

    I replied to this but somehow didn't send it, I suspect. Thank you for this. It's really helpful, particularly the 'lower limit' for the schools. I think it's a better idea to do the conditional formatting with the interests, rather than the type of school (stretch, etc.), especially laid out as you have done it with the output on the same page as the input.

    I'm off to see if I can find my original reply, as it feels like a lifetime ago that I typed it, and I'm certain that I said some other really important things in it!

    Is there a way to take into account the total number of each type of school requested, as well as to match up mixed/single *** interest preferences even if they prefer a different gendered school?

    Thank you very much.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    Re: Need to enter test scores and return suggestions based on those scores

    I feel that I understand the request, and in order to include both mixed and single gender schools:
    I changed the values in column C of the Input sheet to be F/M/B (Female/Male/Both)
    I also changed the layout of the Schools and Scores sheet to have ranges for only Girls and Boys schools which both include the schools that accept both.
    The formula for Backup is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For Comfortable:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For Stretch:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Schools that specialize in the students first choice are highlighted in green using: =INDEX('Schools and Scores'!$C$2:$C$16,MATCH(T3,'Schools and Scores'!$A$2:$A$16,0))=$D3
    A similar formula is used to highlight schools specializing in the students second choice in blue.
    Let us know if you have any questions.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Need to enter test scores and return suggestions based on those scores

    Wow. Thank you so much. I can't wait to peruse this in more detail. I see that I can eliminate column B from the INPUT sheet. I really can't thank you enough; I expected to need to do a little more of my own work on this. You have saved me hours of work. Thank you, also, to Bernie, who started it off for me.

    I really appreciate it. I hope it was fun for you!

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    Re: Need to enter test scores and return suggestions based on those scores

    Realized that I had left out the activities choice highlighting for the boys.
    Also if gender choice matches the school the font is set to orange using a rule similar to the last one given in post #9.
    Let us know if you have any questions.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transpose rows to columns and sort by scores (with repetitive scores)
    By anishmalhotra in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-02-2019, 08:17 PM
  2. I need a macro to select range of scores, choose the best scores and apply a formula
    By hadleedog in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2015, 03:15 PM
  3. Replies: 8
    Last Post: 12-20-2014, 04:37 PM
  4. Returning a list of names based on their test scores without any gaps
    By teacherphil in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-17-2013, 08:41 PM
  5. [SOLVED] Help ranking tutor groups based on test scores
    By leeus111 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-25-2013, 11:47 AM
  6. Replies: 1
    Last Post: 06-15-2013, 09:02 PM
  7. Pull sub-test scores from test w/best overall test score (PSAT)
    By PowerSchoolDude in forum Excel General
    Replies: 0
    Last Post: 11-19-2009, 08:29 PM

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