CPT 105
Franklin College
Erich Prisner
Download and save the following file.
The task is to get a ranking of the colleges for your friends, based on the data.
| 1 | Freeze the heading rows | 7 |
| 2 | Sort the colleges according to the tuition. | 14 |
| 3 | In Row 42, compute the average of the corresponding columns. | AVERAGE |
| 4 | In Row 43, compute the maximum occuring in each column. | MAX |
| 5 | In column J, get the ratio of average tuition and tuition of the corresponding college. Obviously, the smaller the number the better. Display these numbers with three digits after the decimal point. | 11,5 |
| 6 | In column K, get the ratio of average s-t ratio and s-t ratio of the corresponding college. Obviously, the smaller the number the better. Display these numbers with three digits after the decimal point. | 11,5 |
| 7 | In column L, get the ratio of SAT score of the college and average SAT score of all colleges. Obviously, the larger the number the better. Display these numbers with three digits after the decimal point. | 11,5 |
| 8 | In column N display the product of the three factors in the columns J, K, and L. This product is the score for the college, the higher the better the quality-cost ratio. Display these numbers with two digits after the decimal point. | 10,5 |
| 9 | In column O display the product of the two factors in the columns K and L. This product is the score for the college, the higher the better the quality. Display these numbers with two digits after the decimal point. | 10,5 |
| 10 | Display the data in columns N and O in a chart. The x-axis should be labeled by the college names. | 15 |
| 11 | In column Q, the word "small" should pop up for those colleges where the number of students is less than half the average of the student number of the colleges considered. Use the IF function here. | IF, 11 |
| 12 | In column R, the word "large" should pop up for those colleges where the number of students is more than twice the average of the student number of the colleges considered. Use the IF function here. | IF, 11 |
| 13 | In column T, display the numbers of column N, but only for the small colleges (those colleges with the attribute "small" in column Q) but display them with three digits after the decimal point. Use the IF-function. | IF, 11, 5 |
| 14 | Make a chart for the data in column T, the ranking for small colleges only. | 15 |
| 15 | In column U, display the numbers of column N, but only for the private colleges but display them with three digits after the decimal point. Use the IF-function. | IF, 11, 5 |
| 16 | Make a chart for the data in column U, the ranking for private colleges only. | 15 |
| 17 | For all colleges with average SAT score larger than 1180, the SAT score number should automatically be displayed in red and in bold face. | 8 |
After finishing, please save your file and send it as an email attachment at my email eprisner@fc.edu . Tell me if for some reason that doesn't work. In the worst case, you can also print your file and give me the printout.
Erich Prisner, November 2005