Old Excel Day 3: Functions

Objectives

General:

  • Understand the uses of functions
  • Understand the application of functions in a variety of scenarios

Specific:

  • Function/Formula syntax
  • Apply the IF function to everyday situations
  • Nest IF functions together
  • Use the SUMIF function
  • Use the VLOOKUP function
  • Use the INDEX/MATCH Functions
  • Use named ranges to simplify formulas/functions

Outline

Hook: Show the finished file complete with the data validation

Introduction:

  • Names, positions, goals for class, etc.

Function/Formula syntax

  • Difference between a function and a formula
  • Parts/sections of a function
    • =FUNCTIONNAME (Press tab to auto-start the function)
    • , (Commas)
  • Multiple ways to enter functions
    • Formula bar
    • Straight into the cell (shortcut F2)
    • Formula tab, by category
    • Insert function button
  • Getting help with functions (Google, YouTube, Microsoft Website)

The IF function

  • Go over some daily scenarios that involve if/then thinking (e.g. wearing a jacket if it’s cold)
  • Write out on board the parts/sections of the function

Nested IF Functions

  • Use when checking for multiple criteria (if it was cold and a blizzard, you wear all the winter clothes you own, rather than just a jacket)
  • No need to type the whole function out… copy and paste the first section as many times as you need.
  • If you wanted to change the criteria, you could set up cells that are referenced in the formula.

Use the SUMIF function

  • Absolute references save you a lot of time
  • Copy and paste these functions down and across

Use the VLOOKUP function

  • Use when you want to look up information without searching through your data
  • Using data validation in conjunction with vlookup is a nifty tool

Use the INDEX/MATCH Functions

  • Pros:
    • Faster
    • Can search in any column
    • Can search horizontally or vertically
  • Cons:
    • More complicated to understand
  • Index: Returns a value of a cell reference defined by row/column numbers
  • Match: Looks up a value in a range and returns the relative position (row/column number)

Use named ranges to simplify formulas/functions

  • Instead of having  a range like $A$230:$ZX$5972, you can name the range “Cities”.
  • Simplify formulas and be able to reference ranges quicker

Conclusion

  • Recap:
    • Apply the IF function to everyday situations
    • Nest IF functions together
    • Use the SUMIF function
    • Use the VLOOKUP function
    • Use the INDEX/MATCH Functions
    • Use named ranges to simplify formulas/functions
  • Next time
    • Charts and Printing
  • Office/Facebook

Example Hook

Here’s a completed file for Iggy’s Pest Control: Completed File

Example Questions

What would happen to the vlookup result if we added a space before what we were searching for?

Instead of typing a number in the column section of the index/match function, what could we do to make it automatically adjust the column number when we copy the function? (use the function row)

What decisions did you make this morning?

Example Activities and Files

Have students download Iggy’s Pest Control.xlsx and follow along as you show them how to use functions and formulas (easier)

Have students download Paycheck.xlsx and practice using functions in a real scenario (more complicated)

Other Resources

Old Excel Day 3 Page