# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  Need to enter test scores and return suggestions based on those scores

## deborahlane

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.

----------


## Bernie Deitrick

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.

----------


## deborahlane

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!

----------


## deborahlane

Hi, Bernie,

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

Thank you.
Deborah

----------


## JeteMc

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.

----------


## deborahlane

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

----------


## JeteMc

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: 

```
Please Login or Register  to view this content.
```


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

----------


## deborahlane

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.

----------


## JeteMc

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: 

```
Please Login or Register  to view this content.
```


For Comfortable: 

```
Please Login or Register  to view this content.
```


For Stretch: 

```
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.

----------


## deborahlane

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!  :Smilie:

----------


## JeteMc

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.

----------

