This is for work, and I'm stuck. I'd like to take internal enrollment data and create a "model school profile" derived from an external data-set. But I can't quite figure out how to account for differences in enrollment from each school, and the size of the school itself. (I'm not paid to be good in math, though I'm very interested in figuring this out.)
Here's the first set of data --- enrollment over five years, by school. I feel like the weighted value should be an important tool, but it doesn't seem to account correctly for the variation between school1 & school20.
Here's the second data set,
I'd also like to pull in information about total population from each school participating in this activity, but this information is less important at this time.
I'd very much appreciate your thoughts!
Here's the first set of data --- enrollment over five years, by school. I feel like the weighted value should be an important tool, but it doesn't seem to account correctly for the variation between school1 & school20.
| AY07-08 | AY08-09 | AY09-10 | AY10-11 | AY11-12 | TOTAL | % of total | Weighted value | |
| School1 | 34 | 32 | 38 | 34 | 30 | 168 | 7.0% | 1.7 |
| School2 | 9 | 28 | 27 | 34 | 51 | 149 | 6.2% | 1.62 |
| School3 | 29 | 36 | 32 | 25 | 24 | 146 | 6.1% | 1.61 |
| School4 | 35 | 25 | 32 | 24 | 30 | 146 | 6.1% | 1.61 |
| School5 | 54 | 22 | 17 | 22 | 25 | 140 | 5.8% | 1.58 |
| School6 | 19 | 25 | 13 | 18 | 35 | 110 | 4.6% | 1.46 |
| School7 | 29 | 20 | 23 | 12 | 20 | 104 | 4.3% | 1.43 |
| School8 | 16 | 24 | 16 | 16 | 15 | 87 | 3.6% | 1.36 |
| School9 | 27 | 14 | 12 | 18 | 13 | 84 | 3.5% | 1.35 |
| School10 | 18 | 15 | 17 | 16 | 17 | 83 | 3.5% | 1.35 |
| School11 | 11 | 10 | 14 | 15 | 21 | 71 | 3.0% | 1.3 |
| School12 | 7 | 6 | 13 | 19 | 23 | 68 | 2.8% | 1.28 |
| School13 | 14 | 12 | 16 | 9 | 17 | 68 | 2.8% | 1.28 |
| School14 | 11 | 12 | 15 | 14 | 11 | 63 | 2.6% | 1.26 |
| School15 | 18 | 16 | 12 | 11 | 6 | 63 | 2.6% | 1.26 |
| School16 | 19 | 9 | 10 | 7 | 12 | 57 | 2.4% | 1.24 |
| School17 | 19 | 9 | 11 | 9 | 7 | 55 | 2.3% | 1.23 |
| School18 | 4 | 5 | 11 | 17 | 14 | 51 | 2.1% | 1.21 |
| School19 | 8 | 9 | 6 | 10 | 15 | 48 | 2.0% | 1.2 |
| School20 | 10 | 9 | 5 | 6 | 15 | 45 | 1.9% | 1.19 |
| Total | 391 | 338 | 340 | 336 | 401 | 1806 | ||
| GRAND TOTAL | 496 | 443 | 452 | 468 | 546 | 2405 | ||
| 79% | 76% | 75% | 72% | 73% | 75% |
Here's the second data set,
| Score | Tuition & fees | % of applicants admitted | % out of state students | % in top-10 of graduating class | ||
| School1 | 72 | 41000 | 47% | 68% | 36% | |
| School2 | 73 | 41000 | 40% | 73% | 68% | |
| School3 | 86 | 41000 | 29% | 69% | 73% | |
| School4 | 87 | 43000 | 24% | 75% | 65% | |
| School5 | 82 | 41000 | 22% | 91% | 69% | |
| School6 | 63 | 36000 | 67% | 58% | 39% | |
| School7 | 78 | 40000 | 39% | 85% | 63% | |
| School8 | 87 | 39000 | 18% | 65% | 90% | |
| School9 | 94 | 43000 | 10% | 73% | NA | |
| School10 | 78 | 40000 | 28% | 67% | 81% | |
| School11 | 71 | 33000 | 69% | 58% | 29% | |
| School12 | 89 | 40000 | 21% | 89% | 87% | |
| School13 | 74 | 41000 | 45% | 72% | 63% | |
| School14 | 72 | 38000 | 49% | 73% | 49% | |
| School15 | 43000 | NA | 75% | 46% | ||
| School16 | 69 | 39000 | 38% | 75% | 61% | |
| School17 | 79 | 42000 | 31% | 76% | 65% | |
| School18 | 100 | 29000 | 45% | 79% | 59% | |
| School19 | 60 | 38000 | 51% | 59% | 42% | |
| School20 | 96 | 39000 | 16% | 87% | 84% |
I'd also like to pull in information about total population from each school participating in this activity, but this information is less important at this time.
I'd very much appreciate your thoughts!
