Excel Formula
Management & Presentation
Formulas – Formula
Auditing -- Show Formulas
Size and Print the Document
Student ID |
Exam 1 |
Exam 2 |
Final |
Average |
Variation |
Grade |
356-91-6417 |
75 |
53 |
54 |
60.67 |
-0.20 |
D |
377-46-6379 |
94 |
85 |
78 |
85.67 |
0.13 |
B |
321-40-6343 |
100 |
88 |
82 |
90.00 |
0.19 |
A |
399-01-6558 |
72 |
59 |
55 |
62.00 |
-0.18 |
D |
329-30-6462 |
71 |
58 |
58 |
62.33 |
-0.18 |
D |
309-70-6956 |
79 |
71 |
71 |
73.67 |
-0.03 |
C |
331-54-6867 |
95 |
86 |
84 |
88.33 |
0.17 |
B |
386-56-6542 |
98 |
91 |
83 |
90.67 |
0.20 |
A |
367-23-6735 |
65 |
56 |
54 |
58.33 |
-0.23 |
F |
307-78-6091 |
66 |
56 |
55 |
59.00 |
-0.22 |
F |
346-51-6561 |
80 |
70 |
65 |
71.67 |
-0.05 |
C |
315-16-6063 |
100 |
83 |
83 |
88.67 |
0.17 |
B |
355-16-6207 |
97 |
89 |
82 |
89.33 |
0.18 |
A |
388-15-6186 |
91 |
79 |
76 |
82.00 |
0.08 |
B |
307-06-6032 |
82 |
66 |
67 |
71.67 |
-0.05 |
C |
|
|
|||||
Average |
84.33 |
72.67 |
69.80 |
75.60 |
0.00 |
|
The formulas are
displayed in the same cells for which they compute the derived data. Note that
if you enter the formula “=AVERAGE(B2:D2)” in cell E2
it will compute the average of the contents of cells B2, C2, and D2. You can
then copy the formula to cell E3 thru E16 and the contents of the formula will
be automatically updated for the row in which the new formula resides. For
instance the formula in cell E3 will be automatically changed to “=AVERAGE(B3:D3)”, i.e., it will refer to the contents of cell
B3, C3, and D3.
Student ID |
Exam 1 |
Exam 2 |
Final |
Average |
Variation |
Grade |
356-91-6417 |
75 |
53 |
54 |
=AVERAGE(B2:D2) |
=(E2-$E$18)/$E$18 |
=IF(E2>89,
"A",IF(E2>79, "B", IF(E2>69, "C",
IF(E2>59, "D", "F")))) |
377-46-6379 |
94 |
85 |
78 |
=AVERAGE(B3:D3) |
=(E3-$E$18)/$E$18 |
=IF(E3>89,
"A",IF(E3>79, "B", IF(E3>69, "C", IF(E3>59,
"D", "F")))) |
321-40-6343 |
100 |
88 |
82 |
=AVERAGE(B4:D4) |
=(E4-$E$18)/$E$18 |
=IF(E4>89,
"A",IF(E4>79, "B", IF(E4>69, "C",
IF(E4>59, "D", "F")))) |
399-01-6558 |
72 |
59 |
55 |
=AVERAGE(B5:D5) |
=(E5-$E$18)/$E$18 |
=IF(E5>89,
"A",IF(E5>79, "B", IF(E5>69, "C",
IF(E5>59, "D", "F")))) |
329-30-6462 |
71 |
58 |
58 |
=AVERAGE(B6:D6) |
=(E6-$E$18)/$E$18 |
=IF(E6>89,
"A",IF(E6>79, "B", IF(E6>69, "C",
IF(E6>59, "D", "F")))) |
309-70-6956 |
79 |
71 |
71 |
=AVERAGE(B7:D7) |
=(E7-$E$18)/$E$18 |
=IF(E7>89,
"A",IF(E7>79, "B", IF(E7>69, "C",
IF(E7>59, "D", "F")))) |
331-54-6867 |
95 |
86 |
84 |
=AVERAGE(B8:D8) |
=(E8-$E$18)/$E$18 |
=IF(E8>89,
"A",IF(E8>79, "B", IF(E8>69, "C",
IF(E8>59, "D", "F")))) |
386-56-6542 |
98 |
91 |
83 |
=AVERAGE(B9:D9) |
=(E9-$E$18)/$E$18 |
=IF(E9>89,
"A",IF(E9>79, "B", IF(E9>69, "C",
IF(E9>59, "D", "F")))) |
367-23-6735 |
65 |
56 |
54 |
=AVERAGE(B10:D10) |
=(E10-$E$18)/$E$18 |
=IF(E10>89,
"A",IF(E10>79, "B", IF(E10>69, "C",
IF(E10>59, "D", "F")))) |
307-78-6091 |
66 |
56 |
55 |
=AVERAGE(B11:D11) |
=(E11-$E$18)/$E$18 |
=IF(E11>89,
"A",IF(E11>79, "B", IF(E11>69, "C",
IF(E11>59, "D", "F")))) |
346-51-6561 |
80 |
70 |
65 |
=AVERAGE(B12:D12) |
=(E12-$E$18)/$E$18 |
=IF(E12>89,
"A",IF(E12>79, "B", IF(E12>69, "C",
IF(E12>59, "D", "F")))) |
315-16-6063 |
100 |
83 |
83 |
=AVERAGE(B13:D13) |
=(E13-$E$18)/$E$18 |
=IF(E13>89,
"A",IF(E13>79, "B", IF(E13>69, "C",
IF(E13>59, "D", "F")))) |
355-16-6207 |
97 |
89 |
82 |
=AVERAGE(B14:D14) |
=(E14-$E$18)/$E$18 |
=IF(E14>89,
"A",IF(E14>79, "B", IF(E14>69, "C",
IF(E14>59, "D", "F")))) |
388-15-6186 |
91 |
79 |
76 |
=AVERAGE(B15:D15) |
=(E15-$E$18)/$E$18 |
=IF(E15>89,
"A",IF(E15>79, "B", IF(E15>69, "C",
IF(E15>59, "D", "F")))) |
307-06-6032 |
82 |
66 |
67 |
=AVERAGE(B16:D16) |
=(E16-$E$18)/$E$18 |
=IF(E16>89,
"A",IF(E16>79, "B", IF(E16>69, "C",
IF(E16>59, "D", "F")))) |
|
|
|||||
Average |
=AVERAGE(B2:B16) |
=AVERAGE(C2:C16) |
=AVERAGE(D2:D16) |
=AVERAGE(E2:E16) |
=(E18-$E$18)/$E$18 |
|
·
Read the second chapter in the Excel
book; it is labeled Tutorial 2
·
At the top of the Excel Window, if you
click on the icon fx
an Function Implementation window opens which will guide you to a correct
implementation of the appropriate function; if you have difficulties with the
arguments, there is a Help feature which aids the selection of appropriate
argument values.
·
Under the View option, select the Task
Pane option; you can then type an indicator to get help; e.g., to get help on
loan payment functions, simply type “loan payment functions” and hit the enter
key.
·
If all else fails, see me as I will be
glad to help you.
|