Create a spreadsheet that looks like this. <-(click to get it started)
|
A |
B |
C |
D | E | |
| 1 | Student |
Credits |
Total |
Year | Cost this Term |
| 2 | Susy | 15 | 32 | Freshman | $ 1,080 |
| 3 | Jimmy | 12 | 13 | Freshman | 1,080 |
| 4 | Leo | 9 | 67 | Junior | 810 |
| 5 | John | 10 | 25 | Freshman | 900 |
| 6 | Mike | 5 | 44 | Sophomore | 450 |
| 7 | Brenda | 14 | 100 | Senior | 1,080 |
The values in column A, B and C are your choice. Be sure to have some folk with fewer than 12 "Credits this term" and some with more. Also vary "Total Credits Completed".
The Year is to be calculated based of "Total Credits Completed" as follow
 | Freshman have 32 or fewer credits |
| Sophomores have 33 to 64 credits |
| Junior have 65 to 96 credits |
| Seniors have over 97 credits |
The cost this term must be calculated using $90 per credit with a maximum of $1,080 (12 credits). (Hint, this requires an IF and is the same as the Pancake question: what do I pay if I pay per credit and if it is more than the maximum per term then charge the max otherwise charge the lesser per credit rate.)
This example has 4 students, but assume this spreadsheet is to be used for a real college with hundreds, if not 1,000's of students. Thus, the formula in row 2, columns D and E must work for all rows.
To accomplish this you need to Name the Year VLOOKUP table and the $90 and 12 credits (or $1,080 maximum.)
Print this properly per print Hints.
Change the rules to $100 per person with a $1500 maximum per
term.
This is no effort and the calculated values should change correctly
IF you have done your
IF properly, which is
to compare what the Cost Per Term would be if the student pays the lower of
| the per credit times credit or |
| the term maximum rate. |
Upload to your Web Site Portfolio page
Send me an email with the address of your web site
Hint (read this ONLY if you really need to) this is how I did it
- Type the typed (versus calculated stuff: All of Row 1 and column A to C)
- Type the table to be used in the Year VLOOPUP per the 4 rules above (type it either way to the right, columns H and I or on another sheet)
- Highlight and name the table
- In D8 type "Per credit". In E8 type $90
- In D9 type "Max Charge". In E9 type $1080
- Use Create name (on the Formula Ribbon) to name E8 and E9..
- Do a VLOOKUP in D2 and drag it down
- Put the appropriate formula in E2 and drag it down
- Add a Heading
- Switch to Formula view
- Turn on Print of Gridlines and Row and column Labels
- Print again. Change the Per Credit to $95 and the Max to $1000.
Aren't you proud of yourself!
