HW #7, Management Information Systems (INF1020), Prof. Rosenthal
Description: For
this HW you will continue to modify the GPA spreadsheet that you created in
HW6. This HW has four parts. In part 1, you will add a function to the separate
worksheets for each semester. In parts 2, 3 and 4 you will create a new
worksheet (i.e. tab) that contains summary information for all the semesters.
Part 1 (Deans List): At
the top of the worksheet for each semester
create a cell that says "Deans List:". In the next cell to the right,
create a formula that evaluates to the word "YES" if the
GPA for that semester is greater than or equal to 90. If the GPA is below 90
then the formula should evaluate to the word "NO".
Hint:
use the IF function.
Part 2 (Summary Tab): Create a new worksheet (i.e. tab) and change
the name on the new worksheet to "Summary" (without the quotes). Drag
the tab for the new worksheet so that it is the first tab in the workbook. Add
formulas to the summary tab to calculate the following information:
1. The overall GPA for all semesters
2. The maximum grade received in any course
3. The miniumum grade received in any course
4. The maximum GPA received for any individual semester
5. The minimum GPA received for any individual semester
Make sure that
you include labels on the summary page to describe what is being shown - make
it nice.
Make sure that
your formulas work correctly so that if you change the value of a specific
grade on a specific assignment for a specific semester that all the values
are recalculated including the grade for the course, the GPA for the specific
semester and all of the values on the summary page.
Hint: Don't forget to use the name of the sheet followed by an exclamation point
before a reference to a cell or range of cells on another worksheet (i.e. tab).
(For example: fall2002!C2 or
fall2002!C2:C10)
Part 3 (Overall GPA for all semesters): Add
a formula on the "Summary" worksheet that evaluates to the following:
If the overall GPA for all semesters is greater than or equal to 95, the formula should evaluate to the words "Summa Cum Laude".
Otherwise, if the overall GPA for all
semesters is greater or equal to 92.5 (but less than 95) the formula
should evaluate to the words "Magna Cum Laude".
Otherwise, if the overall GPA for all
semesters is greater than or equal to 90 (but less than 92.5) the formula
should evaluate to the words "Cum Laude".
Otherwise, if the GPA is less than 90
then the formula should evaluate to the empty string, i.e. ""
Hint: you
will need to use "nested" if function calls. A nested function call
is a function call that is a parameter to another function call. For example the following formula contains a
nested IF:
=IF(A1=B2,"EQUAL",IF(A1>B2,"GREATER","LESS
THAN"))
The formula above does the following:
The formula displays the word
"EQUAL" if the value of cell A1 is equal to the value of cell
B2.
The formula displays the word
"GREATER" if the value of cell A1 is greater than the value
of cell B2.
The formula displays the words
"LESS THAN" if the value of cell A1 is less than the value
of cell B2.
The example above has one level of nesting. However, to do this part of your
homework correctly you will need to have several levels of nesting. I
recommend that you get the IF function working for just "Summa Cum
Laude". Then modify the function to work for both Summa and Magna and then
finally for Suma, Magna and Cum Laude.
Extra Credit: For 5 points
extra credit, modify the function so that the student can only graduate with
honors (Suma, Magna or Cum Laude) if they have never received a grade less than
60. Hint: you will need to use the AND function several times in the same
formula. You should compare the lowest grade ever received (this
should be in a cell on the summary page) to the number 60.
part 4 (avg of 16 credits per semester?): In
a cell on the summary tab, write the words : "On track to graduate in 4
years:". Next to that cell create a formula that evaluates to the word
"YES" if the average number of credits taken each semester
is 16 or more. Otherwise the formula should evaluate to the word
"NO".
Hint:
- You will need to use the IF function.
- The first parameter to the IF
function should include a call to the AVERAGE function.
- The AVERAGE function should have several parameters each of
which should refer to a cell that contains the total credits for
a specific semester.
- Inside the IF, you will need to compare the value of the AVERAGE function
call to the number 16. If the value of the AVERAGE function is greater
than or equal to 16 then the IF function should evaluate to "YES"
otherwise the IF function should evaluate to "NO".