Excel 2013: Understanding Formulas and Functions from scratch
Gilberto Berlanga, International Relationist / Industrial Engineer


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! :)