Skip to main content

Excel Chapter 2

Chapter Demonstrable Skills

After completing the Excel Chapter 2 portion of the Excel & Data Analysis unit, students will be able to:

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 C
  • Homework #4
  • Homework #5
  • Homework #6
  • MyLab Lesson C
Use absolute, mixed, and relative cell references in formulas.
  • Excel: Formulas & Functions I Participation Project
  • MyLab Lesson A
  • Homework #3
  • 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
  • Homework #2
  • MyLab Lesson A
  • MyLab Bonus Project #1
Use logical functions such as IF.
  • Excel: Formulas & Functions II Participation Project
  • MyLab Lesson A
  • Homework #2
  • MyLab Lesson A
Use lookup functions such as 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

Chapter Notes

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")

Tips & Tricks

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

Cell references example

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

References

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