Lab 8: Excel Functions And Formulas Cheat Sheet Page 2

ADVERTISEMENT

spreadsheet: Click the mouse in a cell directly below the titles and right of the names
column (D3) and choose Freeze Panes from the Windows menu.
7. Now let’s set up some lab grade columns. The next five columns can all be lab grades,
but just for the sake of using some date arithmetic, let’s label them by date. Type a
date in the first title row for the first lab (e. g. 3/17/00 or March 17, 2000), then enter
a formula in the next column that adds seven to the previous lab’s date. Copy this
formula to the next 3 cells and you should end up with a row of dates, each a week
later than the last. Just for fun, reformat the dates as numbers (using Format/Cells) so
you can see how Excel thinks of them, then UNDO the formatting to get them back to
dates. Put LAB in the second row and copy it across the five labs to make it clear that
they are labs. Fill all the student’s lab grade cells with grades (numeric percentage
grades, not A’s, B’s, etc.). You don’t need to be conscientious about the grades you
give them, just type in some numbers. You can do lots of copying or use the Edit Fill
command to help fill in numbers if you want. Don’t spend a lot of time typing in
numbers, but do try to end up with a variety of grades and not every student the same.
8. The next column will be an overall lab grade. Computing this grade is complicated by
the fact that you promised them you would drop their lowest lab grade, though, so you
can’t just use the average function. Instead, you can add them up, subtract the lowest,
then divide by the number of grades you are averaging. Use SUM to add them up and
MIN to find the lowest. Subtract the MIN from the SUM, then divide by 4 (be sure to
use parentheses to make sure it does the operations in the correct order).
9. The next column will be a midterm grade. Let’s just give them all a midterm grade
and not do anything fancy with it.
10. Unfortunately, they did really badly on the final exam so you will have to curve it
because you don’t like giving bad grades. Enter their horrible grades in one column,
but make another column for adjusted grades. At the bottom of the actual grade
column, make a cell that averages all the grades. Create a formula in the adjusted
grade column which takes their actual grade and adds to it the difference between the
class average and 80. If the actual grades were in column I and the average in row 17
the formula would go something like this: =I3+80-$I$17. Note the use of the
absolute address to refer to the class average. Because this could give a grade much
higher than 100 for the few students who did well, you can limit the final exam grade
to a maximum of 100 using the MIN function. Simply use the formula above as one
argument of the function and 100 as the other and MIN will return whichever is
lowest: =MIN(I3+80-$I$17, 100).
11. Compute their overall average in the final column, weighting the labs 50%, the
midterm 20% and the final 30%. You can do this simply by multiplying the lab grade
by .5, the midterm by .2, and the final by .3 and adding them up.

ADVERTISEMENT

00 votes

Related Articles

Related forms

Related Categories

Parent category: Education
Go
Page of 3