Excel Chapter 2

Demonstrable Skill	Learning Activities & Informal Assessments	Formal Assessments (excluding Exams)
Design and create queries to extract desired information.	Access: Access Basics Participation Project Access: Queries I Participation Project Access: Queries II Participation Project Access: Queries III Participation Project Access: Queries IV Participation Project MyLab Lesson D	Homework #3 Homework #4 Homework #5 MyLab Lesson D
Use absolute, mixed, and relative cell references in formulas.	Excel: Formulas & Functions I Participation Project MyLab Lesson A	Homework #2 MyLab Lesson A
Use basic math and statistical functions such as SUM, AVERAGE, MEDIAN, MIN, MAX, and COUNT.	Excel: Formulas & Functions I Participation Project MyLab Lesson A	Old Homework #2 MyLab Lesson A MyLab Extra Credit Project #1
Use logical functions such as IF.	Excel: Formulas & Functions II Participation Project MyLab Lesson A	Old Homework #2 MyLab Lesson A
Use lookup functions such as XLOOKUP, VLOOKUP, and HLOOKUP.	Excel: Formulas & Functions III Participation Project MyLab Lesson B	MyLab Lesson B
Calculate payments with financial functions such as PMT and FV.	Excel: Formulas & Functions III Participation Project MyLab Lesson B	MyLab Lesson B

Cell references

A normal relative cell reference that changes when you copy it will look like this: A1.

An absolute cell reference will look like $A$1 with the $ telling Excel not to change the row or column when it is copied. If you copy a relative cell reference like A1 down one cell, it will change to B1. An absolute reference like $A$1 will not change when copied or AutoFilled [2].  

Function wizard

Chapter 2 covers basic math and statistical functions like SUM, AVERAGE, MEDIAN, MIN, MAX, and COUNT [1].

If you use the Insert Function Wizard, you can have Excel write these formulas for you. All you will have to do is select the cell range. Excel will often even suggest a range to use.  

IF statements

The IF function is used to evaluate statements. An IF statement consists of three parts - a condition, what happens if true, and what happens if false [1].

Condition:A1>60
If True: Pass
If False: Fail

Put together, the statement would read like this:

=IF(A1>60, "Pass", "False")

Pay attention to the user interface

Always pay attention to what Excel offer to select for you when using the basic functions and the AutoSum button from the ribbon [2]!

Cell references

Let's take a close look at absolute references [2]. We'll start with a regular relative reference: 

=B6*((1+B3)^(C5-B5))

Let's modify it with absolute references. On Homeworks and Exams, you might encounter a line like this asking you to modify the basic formula (above) to include absolute references (below).

Don't just copy the base formula! You need to modify it!

• The first B6 should be $B6 because it is column-absolute (the $ goes in front of the column reference to keep it from changing) 
• B3 should be fully absolute $B$3 with $ in front of both to keep both from changing.
• C5 should be C$5 because it is row absolute with the $ in front of the row reference to keep it from changing.
• B5 should be fully absolute $B$5 with $ in front of both to keep both from changing
• The formula that you place in cell C6 would look like

 =$B6*((1+$B$3)^(C$5-$B$5))-5

1. A. Poatsy, K. Mulberry, C. Krebs, L. Hogan, A. Rutledge, and E. Cameron, Exploring Microsoft Office 2013, 1st ed., vol. 1. Upper Saddle River, NJ: Pearson Education, 2013.
2. R. G. Phipps and B. M. Powell, “Excel Chapter 2,” Computer Science 101, May 18, 2016. Available: http://cs101.wvu.edu/materials/units/excel-data-analysis/excel-chapter-2/.