Find the average SAT scores of all student who applied to each college

[jefhal]

This is a beaut! Our Student Services department keeps track of all th
colleges to which any senior applies.

Simplified assumptions/knowns:

Sheet 1 lists students by name (Col1) and colleges applied to (Column
2 through 10). Column 11 is the name of the college that they are goin
to. The student's SAT scores are in Column 12.

Sheet 2 is the list of Colleges (probably 300 different ones thi
year). Column 1 is the name of the College. Column 2 should be th
average SAT score for all who applied there. Column 3 should be th
average SAT score for all who were accepted and who plan to atten
there. (They tell us this when they make their decision)

What is the formula needed in columns 2 and 3 on sheet 2? Can I do thi
with only worksheet functions without programming?

I've experimented with vlookup, match, index, etc. but none seem t
allow me to find more than one occurance of a college and then grab th
SAT score when I find each occurance.

The attached xls file has some sample data and lots of dead en
formulas...

Thank you!

JH, Tech Support
Souhegan High School
Amherst, NH
US

Attachment filename: lindamerrill college spreadsheet.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=68508

 

[duane]

you can do this by a sumif(range, school name, range o
scores)/countif(range, school name)

repost if you need more help

 

[Domenic]

If I understand you correctly, assuming that for both sheets the firs
row contains your headers and your data begin on the second row, pu
the following formulas on Sheet 2...

B2, copied down:

=AVERAGE(IF(Sheet1!$B$2:$J$100=Sheet2!A2,Sheet1!$L$2:$L$100))

C2, copied down:

=AVERAGE(IF(Sheet1!$K$2:$K$100=Sheet2!A2,Sheet1!$L$2:$L$100))

Both formulas need to be entered using CTRL+SHIFT+ENTER. Adjust th
range accordingly.

Hope this helps

 

[Don]

Hi Domenic,

Thanks for replying to this post....Just a beginner here
and I've been looking at this all day. I did come up with
an answer but it was way too complex. Nice job.....have
checked both your formulas and both work very cleanly.

Don

 

[Don]

Hi Duane,

Are you sure about this...I can't get SumIf to accept
multiple column ranges.

What might I be doing wrong?

Don

 

[jefhal]

I'm not sure why I chose to test the average(if method first, but, as i
worked like a charm, I stopped there. My heartfelt thanks to everyone o
this thread for being so smart and so generous! I'll be showing th
Student Services people the solution on Tuesday and will give you ful
credit for making this work..

 

[duane]

yes sumif and countif only work on one column at a time, but you can d
countif(...)+countif(....)....etc

 

[Domenic]

Originally posted by Don

Hi Domenic,

....Just a beginner here
and I've been looking at this all day. I did come up with
an answer but it was way too complex.

Been there many times myself.

Cheers

 

[Domenic]

Jeff,

One other thing to keep in mind. If by mistake a college applied fo
is named twice for any one student, then the results returned by th
formula will be incorrect.

So you may want to think about using Data Validation to prevent th
same college from being entered a second time.

 

[jefhal]

Hi Domenic-

As I was already using DataValidation to change colors on th
spreadsheet, I used the brute force method in a set of parallel column
to do the duplication checking. I used CountIF to see how man
occurances of a value appeared in the student's fields. As there were
fields, I had to do five checks for each student. It's not pretty, bu
it works...

Thanks again,

Jef

 

[Domenic]

*As I was already using DataValidation to change colors on th
spreadsheet...*

Do you mean Conditional Formatting? If so, then you could continue t
use Conditional Formatting to hide !DIV/0 errors, and then use Dat
Validation to prevent a user from entering the name of a college
second time.

Data Validation...

1) Select your data (Columns 2 through 11)

2) Data > Validation > Settings > Allow > Custom > Formula

3) Enter the following formula:
=COUNTIF($B2:$J2,B2)=1

Then, if you like, you could go to the "Error Alert" tab and enter
message you'd like to have appear when a user tries entering a name o
a college for the second time. You could have something like..."Thi
college has already been entered!"

Hope this helps

 

[jefhal]

Hi Domenic-

I meant to say Data Validation, but I connected it with the wron
function in my spreadsheet. I used Data Validation in the AppliedT
fields to create a drop down list of colleges to choose from (I use
the list of colleges on the second sheet). Therefore, I do not believ
I can use Data Validation twice to prevent duplicates in the sam
range. I know you can use Conditional Formatting three times on th
same range, but not Data Validation, or am I incorrect?

Sorry for the misleading comment in previous message...

Jef

 

[Domenic]

*...I know you can use Conditional Formatting three times on the same
range, but not Data Validation, or am I incorrect?*

As far as I know, I think you're right. In that case, you can use
Conditional Formatting to highlight duplicates.