J
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
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