Excel Formula Management & Presentation

 

  1. For all spreadsheets and workbooks submitted for grading, print all the formulas used in the document via the following procedure.

 

Formulas – Formula Auditing -- Show Formulas

Size and Print the Document

 

  1. The following spreadsheet illustrates the use of formulas in the computing of derived or dependent data from original or independent data. In the spreadsheet below, the headers or titles all have a green background; the independent data which is input by the user has a yellow background. The derived or dependent data which is computed from the original data has a red background.

 

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

 

 

  1. This is the same spreadsheet with the formulas displayed via the Formula Auditing Mode discussed above

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.

 

  1. Notice the use of the “$” in formulas such as =(E2-$E$18)/$E$18; this allows the copying of the formula without the cell references changing. The use of the “$” sign is often referred to as “absolute cell references”. For example when the formula is copied from cell F2 to F3, F4, …, the reference E2 changes to E3, E4, …, but $E$18 does not change. The “$” sign in front of the letter “E” prevents the “E” from being changed if the formula was copied to a different column; the “$” sign in front of the number “18” prevents it from being changed when copying to different rows.

 

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

 

 

  1. Here is a link to the Grade document, so that you can get a hands-on feel for the effect of changing the independent data, modifying the formulas, displaying & printing the formulas, and the destructive nature of plugging data into dependent cells.

 

  1. In order to understand the financial  and other formulas available in  Excel, you should pursue the following options:

 

·         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.

 

  1. Stay tuned, other information to follow on this site!