Highline Excel 2016 Class 02

Excel Fundamentals: Comprehensive Formula Lesson (15 Examples)

DESCRIPTION

In this video learn about:
(00:13) Introduction
(01:09) Excel’s Golden Rule
(04:31) Look at list of Formula Types and Formula Elements
(06:29) Number Formula Examples
(08:24) Text and Number Formulas for counting numbers above a hurdle
(11:15) When to combine Comparative Operator and Number in a cell or when to combine Comparative Operator and Number in the formula.
(12:36) DOLLAR function.
(14:09) How to combine Comparative Operator and Number in the formula.
(16:53) VLOOKUP function and Array Constant
(22:31) IF Function
(25:09) Logical Formula
(27:06) Aggregate Calculation / Aggregate Formula
(28:12) Array Calculation / Array Formula
(32:40) Excel Formula Order of Precedence (Excel’s Order of Operations)
(37:05) Tracking Down Errors in Excel: 4 Step Method.
(40:37) Types of Errors
(46:19) When to use ROUND function
(49:43) 3 ways to enter cell references into formulas
(53:22) Summary
(54:25) Homework

Highline Excel 2016 Professional Class taught by Michael excelisfun Girvin

WORKBOOKS

Highline Excel 2016 Class 02 Workbook

WRITTEN NOTES

Topics:

  • Excel’s Golden Rule
  • Types of Formulas in Excel
  • The types of Formula Elements that are allowed in formulas are
  • Calculations in Excel
  • Four Steps to finding errors (wrong answer or error message) in formulas
  • Types of Error Messages
  • 3 ways to enter cell references into formulas
  • When to use ROUND function
  • IF Function
  • VLOOKUP function
  • COUNTIFS, SUMIFS, AVERAGEIFS and similar functions
  • LARGE and SMALL functions
  • Important Keyboards for Formulas
  • Important Mouse Tricks for Formulas
  • Cumulative List of Keyboards Throughout Class

Excel’s Golden Rule:

See Excel’s Golden Rule post

Formula Types, Formula Elements, How Formulas Calculate, Formula Errors

Types of Formulas in Excel:

  • Number formulas that deliver a single number answers such as a tax deduction amount or a budgetary expense amount.
  • Text formulas deliver a text item such as a name or category. The Data Type is always Text.
  • Logical formulas (Boolean Formulas) deliver a TRUE or FALSE.
  • Lookup formulas lookup a particular item in a table and return an item such as a phone number or tax rate.
  • Aggregate formulas are formulas that take two or more items and calculate a single answer such as add a bunch of numbers to get a single total answer.
    • We will say that we have made an “aggregate calculation” or an “aggregate operation”.
  • Array formulas are formulas that calculate on an array of items rather than single items and which deliver an array of items as a result of the “array calculation”.
    • We will say that we have made an “array calculation” or an “array operation”.

The types of Formula Elements that are allowed in formulas are:

  • Equal sign.
  • Cell references (also defined names, sheet references, workbook references.
  • Table Formula Nomenclature (Structured References).
  • Math operators.

2-1

  • Numbers (if they won’t change)
  • Built-in Functions.
  • Function argument elements, such as TRUE or FALSE in VLOOKUP function to indicate Approximate or Exact Match.
  • Comparative operators.

2-2

  • Join symbol: Ampersand (&).
  • Text within quotation marks.
  • Array constants.
    • Curly Brackets house the array: { }
    • Comma means column ,
    • Semi-colon means row :
    • Example of a table array: {“Jo”,235.56;”Sioux”,52;”Chin”,365.21}

Calculations in Excel:

Aggregate Calculations:

    Calculations that take two or more items and calculate a single answer such as:

    • Adding a column of number to get a total sum.
    • Counting how many names are in a column to get a total count.

Array Calculations:

    Calculations that operate on an array of items rather than single items and which deliver an array of answers called a “resultant array”.

    • Function argument array operation like: = LARGE(C98:C106,{1,2,3}), where we are putting the array {1,2,3} into the k argument of LARGE and LARGE will then deliver the 1st, 2nd and 3rd largest values in a resultant array like this: {278,268,245}.
    • Single Cell * Column of Numbers
    • Column of Numbers * Single Cell
    • Column of Numbers * Column of Numbers

How Formulas Calculate: Order of Precedence in Excel:

  • Parenthesis ( )
  • Reference Operators: colon, space, comma
    • Example of colon in range of cells: =SUM(A1:A4)
    • Example of intersection operator: =E12:G12 F10:F15 (retrieve what is in F12)
    • Example of comma (union): =SUM(E10:G10,E14:G14)
  • Negation (-)
    • Example: = -2^4 = 16
    • Example: = -(2^4) = -16
    • Example: –2+1 = 3
  • Converts % (1% to .01)
  • Exponents (^)
    • Example: 4^(1/2) = 2
    • Example: 3^2 = 9
  • Multiplication (*) and Division (/), left to right
  • Adding (+) and Subtracting (-), left to right
  • Ampersand (&)
  • Comparative symbols: =, <>, >=, <=, <, >

Four Steps to finding errors (wrong answer or error message) in formulas:

See Error Messages post

3 ways to enter cell references into formulas:

1. Type: Be careful, though, errors may occur.
2. Mouse: Great for when cell reference is far away.
3. Arrow keys: great for when cell references are close.

  • As long as “Dancing Ants” are dancing, the arrow keys will keep moving the cell reference.
  • If Arrow keys do not put cell reference into formula:
      Lower left corner of Status Bar shows the “Formula Editing Mode”
      You can toggle between these modes with the F2 key.

      • Ready = cell is selected.
      • Enter = Arrow keys will work to put cell references in formulas.
      • Point = Arrows keys are working to get cell reference and “Dancing Ants” are dancing.
      • Edit = Arrow keys will move through formula characters. To get into this mode click I-beam cursor in formula.

When to use ROUND function

  • We can use the ROUND function to remove unwanted decimals using the standard rounding rule.
  • Rules for second argument in ROUND:
    • Round to penny (hundredths position) use 2
    • Round to dollar (one’s position) use 0
    • Round to thousands (thousands position) use -3
  • When to use ROUND:
    • You MUST use ROUND when:
      • You are required to round, like with money.
      • You will use the formula result in subsequent calculations.
    • Clue you might need to round:
      • You are multiplying or dividing numbers with decimals, like multiplying an amount times a tax rate.
    • If you are NEVER going to use a number in any subsequent formulas, you can display the number the way you want with Number Formatting.
    • Examples of when you usually need to round:
      • Payroll
      • Invoices
      • Taxes

More examples ROUND Function

IF function

  • Use IF function to put one of two things into a cell or formulas:
    • Two Numbers.
    • Two Words (Text).
    • Two Formulas.
    • Two anything.
  • logical_test argument gets the Logical Formula that comes out to be TRUE or FALSE.
  • value_if_true argument is what will go in cell if Logical Formula gets a TRUE.
  • value_if_false argument is what will go in cell if Logical Formula gets a FALSE.

More examples IF Function

VLOOKUP function

  • Use VLOOKUP function to retrieve something from a table and brings it back to the cell
  • V means Vertical.
  • VLOOKUP: =VLOOKUP( lookup_value , table_array , col_index_num , [range_lookup] )
    • lookup_value = thing you look at BEFORE you go over to the table.
    • table_array = vertical table = VLOOKUP table
    • col_index_num = which column in the table has the thing you want to go and get and bring back to the cell.
    • [range_lookup] = Exact Match = FALSE or 0. Approximate Match = TRUE or 1 or omitted.
      • Exact Match:
        • VLOOKUP will look though each item in the first column of the VLOOKUP table and try to find a match
        • If VLOOKUP cannot find a match it will be polite and say that it is not available: #N/A!
        • If you are doing Exact Match use: Data Validation List:
        • Data Ribbon Tab, Data Tools, Data Validation, Allow textbox = List, Source = first column of VLOOKUP table_array.
      • Approximate Match:
        • For Approximate Match the VLOOKUP table MUST be sorted on the first column: Ascending, A to Z, Small to Big.
        • For Approximate Match VLOOKUP will race through the first column:
          • If the first value in the table is smaller than the lookup_value, VLOOKUP returns a Not Available Error: #N/A!
          • It looks through the first column until it bumps into the first value bigger than it and then jump back one row (it actually does: binary search which is fast compared to Exact Match).
          • If the lookup_value is bigger than the last value, it stops at the last row.

More examples VLOOKUP Function

COUNTIFS function

  • Count with 1 or more conditions/criteria.
  • Does an AND Logical Test (uses AND Criteria) where all criteria must evaluate to TRUE.
  • criteria_range argument will contain the range (cells, Defined Names, or Table References) with all the criteria.
  • criteria argument contains the condition or criteria that tells the function what to count.
    • Conditions and criteria can be:
      • Text (like “West”).
      • Numbers: numbers, Dates, Times.
      • Logical values (TRUE or FALSE).
      • Numbers (or Text) with comparative operators:
        • Example: >=1000 can be used as criteria
          • You can type >=1000 in a cell and refer to it in formula with cell reference.
          • You can type 1000 in cell and join the comparative operator to the cell reference that points to the number like: “>=”&F30, where:
            • Number is in cell F30 (for this example)
            • Comparative operator must be in double quotes
    • You can enter up to 126 range/criteria pairs.

More examples COUNTIFS Function

SUMIFS function

  • Add with 1 or more conditions/criteria
  • Does an AND Logical Test (uses AND Criteria) where all criteria must evaluate to TRUE.
  • sum_range argument will contain the range (cells, Defined Names, or Table References) with the numbers.
  • criteria_range argument will contain the range (cells, Defined Names, or Table References) with all the criteria.
    • criteria argument contains the condition or criteria that tells the function what to add.
    • Conditions and criteria can be:
      • Text (like “West”)
      • Numbers: numbers, Dates, Times
      • Logical values (TRUE or FALSE)
      • Numbers (or Text) with comparative operators:
        • Example: >=1000 can be used as criteria
          • You can type >=1000 in a cell and refer to it in formula with cell reference.
          • You can type 1000 in cell and join the comparative operator to the cell reference that points to the number like: “>=”&F30, where:
            • Number is in cell F30 (for this example)
            • Comparative operator must be in double quotes
      • You can enter up to 126 range/criteria pairs.

More examples SUMIFS Function

LARGE function

    LARGE function gets the kth largest value

Examples:

    • k = 1 gets the maximum value
    • k = 2 gets the second biggest value
    • k = 3 gets the third biggest value
    • k = {1,2,3} simultaneously gets the 3 biggest vales.

More examples LARGE Function

SMALL function

    SMALL function gets the kth largest value

Examples:

    • k = 1 gets the minimum value
    • k = 2 gets the second smallest value
    • k = 3 gets the third smallest value
    • k = {1,2,3} simultaneously gets the 3 smallest vales.

More examples SMALL Function

Important Keyboard Keys for Formulas:

Arrow Key = If you are making a formula, Arrow key will “hunt” for Cell Reference.
F4 Key = If you are in Edit mode while making a formula AND your cursor is touching a particular Cell Reference, F4 key will toggle through the different Cell References:
A1 = Relative
$A$1 = Absolute or “Locked”
A$1 = Mixed with Row Locked (Relative as you copy across the columns AND Locked as you copy down the rows)
$A1 = Mixed with Column Locked (Relative as you copy down the rows AND Locked as you across the columns)
F2 Key = Puts formula in Edit Mode and shows the rainbow colored Range Finder.
F2 Key to toggle between the different Formula Edit Modes:
If Arrow keys do not put cell reference into formula:
Lower left corner of Status Bar shows the “Formula Editing Mode”
Ready = cell is selected.
Enter = Arrow keys will work to put cell references in formulas.
Point = Arrows keys are working to get cell reference and “Dancing Ants” are dancing.
Edit = Arrow keys will move through formula characters. To get into this mode click I-beam cursor in formula.
You can toggle between these modes with the F2 key.
Tab key = When you are selecting a Function from the Function Drop-down list, you can select the function that is highlighted in blue by using the Tab key.
F9 Key = To evaluate just a single part of formula while you are in edit mode, highlight part of formula and hit the F9 key.
If you are creating an Array Constant in your formula: Hit F9.
If you are evaluating the formula element just to see what that part of the formula looks like, REMEMBER: to Undo with Ctrl + Z.
Esc Key = If you are in Edit mode in a Cell, Esc will revert back to what you had in the cell before you put the Cell in Edit mode.
Evaluate Formula One Step at a Time Keyboard: Alt, M, V

Important Mouse Tricks for Formulas:

To copy a formula down a column you can use your Cross Hair or “Angry Rabbit” Cursor to Double-Click the Fill Handle.
How the Double-Click works:
If there is cell content below the formula that you are copying, it copies down to the last cell with content, otherwise it copies the data down to the bottom of the data set (whichever column is taller on the left or right).
In a Function Screen Tip, if you click on the argument name, the entire contents of that argument will be highlighted.

Cumulative List of Keyboards Throughout Class:

Esc Key:
i. Closes Backstage View (like Print Preview).
ii. Closes most dialog boxes.
iii. If you are in Edit mode in a Cell, Esc will revert back to what you had in the cell before you put the Cell in Edit mode.
F2 Key = Puts formula in Edit Mode and shows the rainbow colored Range Finder.
SUM Function: Alt + =
Ctrl + Shift + Arrow = Highlight column (Current Region).
Ctrl + Backspace = Jumps back to Active Cell
Ctrl + Z = Undo.
Ctrl + Y = Undo the Undo.
Ctrl + C = Copy.
Ctrl + X = Cut.
Ctrl + V = Paste.
Ctrl + PageDown = expose next sheet to right.
Ctrl + PageUp = expose next sheet to left.
Ctrl + 1 = Format Cells dialog box, or in a chart it opens Format Chart Element Task Pane.
Ctrl + Arrow: jumps to the bottom of the “Current Region”, which means it jumps to the last cell that has data, right before the first empty cell.
Ctrl + Home = Go to Cell A1.
Ctrl + End = Go to last cell used.
Alt keyboards are keys that you hit in succession. Alt keyboards are keyboards you can teach yourself by hitting the Alt key and looking at the screen tips.
Create PivotTable dialog box: Alt, N, V
Page Setup dialog box: Alt, P, S, P
Keyboard to open Sort dialog box: Alt, D, S
ENTER = When you are in Edit Mode in a Cell, it will put thing in cell and move selected cell DOWN.
CTRL + ENTER = When you are in Edit Mode in a Cell, it will put thing in cell and keep cell selected.
TAB = When you are in Edit Mode in a Cell, it will put thing in cell and move selected cell RIGHT.
SHIFT + ENTER = When you are in Edit Mode in a Cell, it will put thing in cell and move selected cell UP.
SHIFT + TAB = When you are in Edit Mode in a Cell, it will put thing in cell and move selected cell LEFT.
Ctrl + T = Create Excel Table (with dynamic ranges) from a Proper Data Set.
Keyboard to name Excel Table: Alt, J, T, A
Tab = Enter Raw Data into an Excel Table.
Ctrl + Shift + ~ ( ` ) = General Number Formatting Keyboard.
Ctrl + ; = Keyboard for hardcoding today’s date.
Ctrl + Shift + ; = Keyboard for hardcoding current time.
Arrow Key = If you are making a formula, Arrow key will “hunt” for Cell Reference.
Ctrl + B = Bold the Font
Ctrl + * (on Number Pad) or Ctrl + Shift + 8 = Highlight Current Table.
Alt + Enter = Add Manual Line Break (Word Wrap)
Ctrl + P = Print dialog Backstage View and Print Preview
F4 Key = If you are in Edit mode while making a formula AND your cursor is touching a particular Cell Reference, F4 key will toggle through the different Cell References:
A1 = Relative
$A$1 = Absolute or “Locked”
A$1 = Mixed with Row Locked (Relative as you copy across the columns AND Locked as you copy down the rows)
$A1 = Mixed with Column Locked (Relative as you copy down the rows AND Locked as you across the columns)
Ctrl + Shift + 4 = Apply Currency Number Formatting
Tab key = When you are selecting a Function from the Function Drop-down list, you can select the function that is highlighted in blue by using the Tab key.
F9 Key = To evaluate just a single part of formula while you are in edit mode, highlight part of formula and hit the F9 key.
If you are creating an Array Constant in your formula: Hit F9.
If you are evaluating the formula element just to see what that part of the formula looks like, REMEMBER: to Undo with Ctrl + Z.
Alt, E, A, A = Clear All (Content and Formatting)
Evaluate Formula One Step at a Time Keyboard: Alt, M, V




Be the first to comment

Leave a Reply