Have any of you developed a spreadsheet to compare schools and their programs? What types of headers did you use? What was important for you to include?
Son is a rising B-average junior interested in Education (who likes sports -- soccer, lacrosse, but not good enough for college ball), so we have lots of options, but I was curious as to what others of you are using to evaluate schools and programs.
Here were my starting headers:
location - rural, suburbs, urban
student # (big or small school)
% who get in (from CollegeBoard.com)
tuition/room/board
SAT range
Our school printed out a neat little spreadsheet to use for comparing schools. Here's the categories:
1. Academic program and atmosphere
2. Student-faculty ratio
3. Access to professors outside of class
4. Faculty teaching reputation
5. Opportunities for independent study
6. Opportunities for international study
7. Opportunities for internships
8. Academic counseling
9. Campus layout
10. Academic facilities (classrooms, labs, and prc rooms)
11. Availability of computers/interest
12. Library, or research areas
13. Cultural facilities (concert halls, theatres, etc.)
14. Opportunities for visiting lecturers
15. Opportunities for artists and performers
16. Personal counseling availability
17. Recreational facilities (pools, gyms, tracks)
18. Student health facilities
19. Location of campus and surrounding area
20. Size of student population
21. Part-time work availability
22. Clubs, sports, and campus activities
23. Housing availability and options
24. Social life/entertainment
25. Food
Obviously a lot of these might not be that important, so I'd also include:
26. Tuition and misc. costs
27. College's reputation (historically female/black/so on)
28. Political influence (ultra liberal, ultra conservative)
29. Financial aid availability
30. Campus safety
31. Geographic location (climate, long distance)
32. Standardized test averages, typical student profile
34. Retention rate
35. Rankings and list (both official and unofficial) for academics, social life, so on
39. Religious affiliation
I think that covers most of the basics. Good luck!
anytime you rely on their website, you end up getting the same super-positive answer from all of them.
Mine is much simpler- location, cost, degree offered, admissions requirements, and a two general columns where I list whatever positives and negatives I'm aware of. Of course, mine is a list of grad schools, so it's already been pared down considerably from what most undergrads would have.
collegehelpPosts: 6,298Registered UserSenior Member
distance from home (use mapquest)
US News rank
peer assessment score
student-faculty ratio
SAT 75th percentile
percent in top 10% of class
percent of classes under 50
graduation rate
Gourman rank of your major if available
US News rank of your major if available
professors accessible rating from Princeton Review
professors interesting rating from Princeton Review
urban-suburban-small town-rural (assign numerical score)
avarage amount of institutional grant divided by cost of attendance (IPEDS)
personal appeal score ("feel", "intangibles")
quality of life rating from Princeton Review
academics rating from Princeton Review
size of school
region of country
LAC or University
proportion of undergraduates
You can assign weights to these scores based on their importance to you and calculate a single index score for your own personal subjective rating system. Be sure to take into account the size of the number and "direction" of the scoring with pluses and minuses for the weights. For example, "distance from home" might be weighted minus point two. Or, perhaps there is an optimal distance from home, like 100 miles. You might have to play around with the weights until it starts to make sense.
If a particular factor has an optimal level for you, you can weight the factor by how far it is from optimal. Say an SAT 75th percentile of 1300 is optimal for you. Then you could give 1300 the highest weight and decrease the weight in proportion to difference from 1300.
It is an interesting and fun exercise. It can be done in Excel with formulas.
collegehelpPosts: 6,298Registered UserSenior Member
you type in the cell where you want the calculation to go, using the cell designations from the spreadsheet
example: the data for aardvark college is in row 4
column C through N are for the various factors e.g. column C might have average SAT, column d might have distance from home, and so on
You type in the cell where you want the final score to go say row 4 column P
=(c4*1.2)+(d4*(-.2))+(e4*7.5)+...+(n4*20.1)
Your personal, subjective rating will print in that cell. Then you can sort the entire spreadsheet by any of the data columns to see how the schools rank. Be sure to highlight the entire spreadsheet before you sort.
You can copy and paste the formulas to the cells below for each college so you only have to type the formula once. Excel automatically changes the row designations for you. It's fun.
ReninDetroitPosts: 1,093Registered UserSenior Member
I got sick of using 'feelings' to evaluate colleges (it wasn't working for me), so I created a more objective spreadsheet and it helped a lot.
My categories were based on what I wanted in a college. I used:
-Availability and quality of theatre program (major, minor and EC involvement) - 15 points
-Availability and quality of public policy program - 10 points
-Availability and quality of interdisciplinary humanities study - 10 points
-Location (10 points)
-Housing (10 points)
-Dining and availability of vegetarian/vegan fare (5 points)
-Academic reputation/perceived quality of education (5 points)
-Bonus (up to 5 points; in here I gave Brown 5 extra because the campus felt so perfect, and I gave W&M 5 extra points because it would be free for me).
The top 5, based on this, were Brown, W&M, Bowdoin, Emory, and University of Chicago.
collegehelpPosts: 6,298Registered UserSenior Member
In post #9 above, the weights should do several things:
(1) they should reflect the importance you place on that factor
(2) they should adjust the direction of the factor by using plus or minus. For example, say distance from home is a negative for you. You want to be close to home. In this case, you would assign a negative weight (minus) so the higher the mileage the lower the score.
(3) they should adjust the number to a scale that is comparable to other factors. For example, say student teacher ratio is 20 to 1 and SAT 75th percentile is 2000. You can start out with a weight of 10 for student faculty ratio and a weight of .1 (point one) for SAT.
20 times 10 = 200
2000 times .1 = 200
Now they are on a comparable scale to begin with. Next step is to adjust for the direction. A high number for student faculty ratio is a bad thing so you assign a negative weight (-10). High SAT is a good thing so you assign a positive weight (+.1). Finally, decide how important the factor is to you. Say that SAT is five times as important as student faculty ratio. So, you increase the weight for SAT by a factor of 5 (+.5).
This piece of your formula would therefore be
"(s/f ratio times -10) plus (SAT 75th percentile times .5)"
You might have to go through some trial and error until it starts making sense.
JyankeesSS2Posts: 2,021Registered UserSenior Member
Is there any way to copy/paste these into the cells so that I don't have to change them every time. It gets somewhat annoying going through 20 of the little formulas just to change the number to the current row, is there an easier way?
Replies to: Spreadsheet to compare schools?
1. Academic program and atmosphere
2. Student-faculty ratio
3. Access to professors outside of class
4. Faculty teaching reputation
5. Opportunities for independent study
6. Opportunities for international study
7. Opportunities for internships
8. Academic counseling
9. Campus layout
10. Academic facilities (classrooms, labs, and prc rooms)
11. Availability of computers/interest
12. Library, or research areas
13. Cultural facilities (concert halls, theatres, etc.)
14. Opportunities for visiting lecturers
15. Opportunities for artists and performers
16. Personal counseling availability
17. Recreational facilities (pools, gyms, tracks)
18. Student health facilities
19. Location of campus and surrounding area
20. Size of student population
21. Part-time work availability
22. Clubs, sports, and campus activities
23. Housing availability and options
24. Social life/entertainment
25. Food
Obviously a lot of these might not be that important, so I'd also include:
26. Tuition and misc. costs
27. College's reputation (historically female/black/so on)
28. Political influence (ultra liberal, ultra conservative)
29. Financial aid availability
30. Campus safety
31. Geographic location (climate, long distance)
32. Standardized test averages, typical student profile
34. Retention rate
35. Rankings and list (both official and unofficial) for academics, social life, so on
39. Religious affiliation
I think that covers most of the basics. Good luck!
Mine is much simpler- location, cost, degree offered, admissions requirements, and a two general columns where I list whatever positives and negatives I'm aware of. Of course, mine is a list of grad schools, so it's already been pared down considerably from what most undergrads would have.
US News rank
peer assessment score
student-faculty ratio
SAT 75th percentile
percent in top 10% of class
percent of classes under 50
graduation rate
Gourman rank of your major if available
US News rank of your major if available
professors accessible rating from Princeton Review
professors interesting rating from Princeton Review
urban-suburban-small town-rural (assign numerical score)
avarage amount of institutional grant divided by cost of attendance (IPEDS)
personal appeal score ("feel", "intangibles")
quality of life rating from Princeton Review
academics rating from Princeton Review
size of school
region of country
LAC or University
proportion of undergraduates
You can assign weights to these scores based on their importance to you and calculate a single index score for your own personal subjective rating system. Be sure to take into account the size of the number and "direction" of the scoring with pluses and minuses for the weights. For example, "distance from home" might be weighted minus point two. Or, perhaps there is an optimal distance from home, like 100 miles. You might have to play around with the weights until it starts to make sense.
If a particular factor has an optimal level for you, you can weight the factor by how far it is from optimal. Say an SAT 75th percentile of 1300 is optimal for you. Then you could give 1300 the highest weight and decrease the weight in proportion to difference from 1300.
It is an interesting and fun exercise. It can be done in Excel with formulas.
I like Excel formulas... very useful. =]
example: the data for aardvark college is in row 4
column C through N are for the various factors e.g. column C might have average SAT, column d might have distance from home, and so on
You type in the cell where you want the final score to go say row 4 column P
=(c4*1.2)+(d4*(-.2))+(e4*7.5)+...+(n4*20.1)
Your personal, subjective rating will print in that cell. Then you can sort the entire spreadsheet by any of the data columns to see how the schools rank. Be sure to highlight the entire spreadsheet before you sort.
You can copy and paste the formulas to the cells below for each college so you only have to type the formula once. Excel automatically changes the row designations for you. It's fun.
My categories were based on what I wanted in a college. I used:
-Availability and quality of theatre program (major, minor and EC involvement) - 15 points
-Availability and quality of public policy program - 10 points
-Availability and quality of interdisciplinary humanities study - 10 points
-Location (10 points)
-Housing (10 points)
-Dining and availability of vegetarian/vegan fare (5 points)
-Academic reputation/perceived quality of education (5 points)
-Bonus (up to 5 points; in here I gave Brown 5 extra because the campus felt so perfect, and I gave W&M 5 extra points because it would be free for me).
The top 5, based on this, were Brown, W&M, Bowdoin, Emory, and University of Chicago.
(1) they should reflect the importance you place on that factor
(2) they should adjust the direction of the factor by using plus or minus. For example, say distance from home is a negative for you. You want to be close to home. In this case, you would assign a negative weight (minus) so the higher the mileage the lower the score.
(3) they should adjust the number to a scale that is comparable to other factors. For example, say student teacher ratio is 20 to 1 and SAT 75th percentile is 2000. You can start out with a weight of 10 for student faculty ratio and a weight of .1 (point one) for SAT.
20 times 10 = 200
2000 times .1 = 200
Now they are on a comparable scale to begin with. Next step is to adjust for the direction. A high number for student faculty ratio is a bad thing so you assign a negative weight (-10). High SAT is a good thing so you assign a positive weight (+.1). Finally, decide how important the factor is to you. Say that SAT is five times as important as student faculty ratio. So, you increase the weight for SAT by a factor of 5 (+.5).
This piece of your formula would therefore be
"(s/f ratio times -10) plus (SAT 75th percentile times .5)"
You might have to go through some trial and error until it starts making sense.
excel will will automatically adjust the formula for each row