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
|Design and create queries to extract desired information.||
|Use absolute, mixed, and relative cell references in formulas.||
|Use basic math and statistical functions such as SUM, AVERAGE, MEDIAN, MIN, MAX, and COUNT.||
|Use logical functions such as IF.||
|Use lookup functions such as VLOOKUP and HLOOKUP.||
|Calculate payments with financial functions such as PMT and FV.||
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 .
Chapter 2 covers basic math and statistical functions like SUM, AVERAGE, MEDIAN, MIN, MAX, and COUNT .
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.
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 .
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 !
Let's take a close look at absolute references . We'll start with a regular relative reference:
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
- 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.
- 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/.