Excel 2013: Understanding Formulas and Functions from scratch

Gilberto Berlanga, International Relationist / Industrial Engineer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
16 Lessons (40m)
    • 1. Introduction to formulas

      4:44
    • 2. Order of operations

      3:42
    • 3. Introduction to Functions

      5:22
    • 4. What are Relative and Absolute references?

      4:34
    • 5. Mouse selection tricks

      1:04
    • 6. Mouse selection trick

      1:04
    • 7. Filling a Range

      0:39
    • 8. Defining a name range

      2:34
    • 9. Clearing a Range

      1:10
    • 10. Selecting 3D Ranges

      1:10
    • 11. SUMIF Function

      1:54
    • 12. COUNTIF Function

      0:59
    • 13. AVERAGE IFS

      3:14
    • 14. AVERAGE IF

      2:03
    • 15. VLOOKUP FUNCTION

      3:42
    • 16. Project Assignment

      2:33

Project Description

Increase your productivity by using functions

For this project you will need to download the attachment and get your hands in action by inserting the corresponding function for each scenario (Worksheet)

1. Download the attachment

2. The workbook has 6 worksheets that you will use for this project (SUMIF, COUNTIF, VLOOKUP, VLOOKUP2, AVERAGEIF and AVERAGEIFS,

3. You must enter the corresponding function for each worksheet, you can insert the functions in the cell that says "Insert the function here"

4.- For  VLOOKUP worksheet, you won't need to insert a function there, but you will need that worksheet in order to insert correctly the VLOOKUP function in the VLOOKUP Worksheet.

For the SUMIF Function:

Range: All the cells containing the information that you want to sum

Criteria: What do we want to sum specifically

Sum Range: The range of cells containing the numbers that we need to sum

For the COUNTIF Function:

Range: All the cells containing the information that you want to count

Criteria: What do we want to count specifically

For the VLOOKUP Function:

Lookup value: What are we looking for:

Table array: Where will Excel look for?

Col Index Number: From which column will Excell pull the values that you need?

Range_lookup: Logical value: True or False

For the AVERAGEIF Function:

Range: The range of cells where our criteria is

Criteria: What is the element that we are focusing on?

Average range: The range of cells containing the actual numbers from which we will take the average from

For the AVERAGEIFS Function:

average_range: The range of cells containing the actual numbers from which we will take the average from

criteria_range_1: The range of cells where our criteria is

criteria 1: The element that we are interedted in

criteria_range_2: The range of cells where our second criteria is

criteria_2: The other element that we are interested in

If you have any questions, please send a message or an email, I will be happy to help! :)

Resources(1)

Student Projects