Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2yLURpp
Download Full Written Notes: http://ift.tt/2maLU35
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:15) Example 1: Excel Table feature for VLOOKUP & Data Validation List
3. (07:10) Example 2: Excel Table feature for Chart
4. (09:45) Example 3: Excel Table feature for PivotTable
5. (13:10) Example 4: Excel Table feature for Other Formulas
6. (16:50) Example 5: Add New Column to an Excel Table AND Create Approximate Match Lookup Table from a written statement.
7. (17:47) Create Approximate Match Lookup Table from Written Instructions
8. (24:35) Change Settings to Allow Columns and Rows to be added to Excel Table as you work: Excel File, Options, Proofing, AutoCorrect, Auto Format As You Type, “Apply As You Work” – “Include New Rows and Columns in Table”
9. (26:10) Add columns to Excel Tables with Table Formula Nomenclature with Relative and Absolute References.
10. (29:30) Formula for Discounts when we need to calculate Net Revenue based on a Discount.
11. (34:14) Add new records to bottom of Excel Table and Formulas will automatically populate down
12. (35:05) Summary – Conclusion
Month: November 2017
Excel Basics 14: Excel VLOOKUP Function Made Easy! 9 Examples to Make Your Job Easier!
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2zHeXRV
Download Full Written Notes: http://ift.tt/2zuiTol
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:10) Should we use IF or VLOOKUP?
3. (02:12) What does VLOOKUP do?
4. (03:22) Examples of Lookup Tables
5. (06:14) What does “V” in VLOOKUP mean?
6. (06:41) Formula 1: Exact Match VLOOKUP to get Product Price & Data Validation Dropdown List: =VLOOKUP(E23,A23:C27,3,0)
7. (09:50) VLOOKUP can do 2 different tyoes of Lookup
8. (11:29) Formula 2: Exact Match VLOOKUP to get Product Price for an Invoice: =VLOOKUP(A41,$A$23:$C$27,3,0)
9. (13:04) Add Data Validation List to a cell to create a drop-down list of valid lookup values for VLOOKUP Function
10. (18:03) Formula 3: Approximate Match VLOOKUP to get a Bonus Commission %: =VLOOKUP(B21,$F$21:$G$27,2)
11. (24:11) Formula 4: Approximate Match VLOOKUP to get a Bonus Commission % & Calculate Bonus Amount: =VLOOKUP(B33,$F$21:$G$27,2)*B33
12. (28:15) What VLOOKUP Does: Puts 1 of multiple things into a cell or formula.
13. (29:01) Formula 5: Exact Match VLOOKUP to get Employee E-mail and Phone Number: =VLOOKUP(A16,A2:I11,6,0)
14. (31:56) Formula 6: Exact Match VLOOKUP to List of Employee Salaries: =VLOOKUP(A23,$A$2:$I$11,9,0)
15. (32:48) Formula 7: Approximate Match VLOOKUP to get Tax Amount: =VLOOKUP(D4,A4:B8,2)
16. (34:23) Formula 8: Approximate Match VLOOKUP to get Commission Amount for Payroll Table: =VLOOKUP(E13,$A$13:$B$17,2)
17. (36:31) Formula 9: Approximate Match VLOOKUP to get Commission Amount for Payroll Table: =VLOOKUP(E4,$H$4:$I$9,2)
18. (38:08) Summary – Conclusion
Excel Basics 13: IF Function Made Easy! IS Functions Too! 14 Epic Examples!!
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2zkj4lL
Download Full Written Notes: http://ift.tt/2zx9ZqF
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Detailed Introduction to the IF Function & Comparative Operators and Logical Tests
2. (08:53) IF Example #01: Your Bonus: =IF(C6 Greater Than =C11,C10,0)
3. (13:39) IF Example #02: In Balance?: =IF(B22=C22,”In Balance”,”NOT In Balance”)
4. (17:16) IF Example #03: Bonus? TRUE or FALSE?: =C4 Greater Than =$F$4
5. (17:54) IF Example #04: Bonus? “Bonus” or “No Bonus”: =IF(C11 Greater Than =$F$11,”Bonus”,”No Bonus”)
6. (18:54) IF Example #05: Bonus? 0 or $750: =IF(C18 Greater Than =$F$18,$G$18,0)
7. (20:20) IF Example #06: Bonus Commission? 5% or 1%: =IF(C25 Greater Than $F$25,$G$25,$H$25)
8. (23:34) IF Example #07: Calculate Commission Paid: =IF(C32 Greater Than $F$32,$G$32,$H$32)*C32
9. (27:46) IF Example #08: “Under” or “Over”: =IF(C39 Less Than $F$39,$H$39,$G$39)
10. (31:25) IF Example #09: “No Balance” or Show Nothing: =IF(C46=0,$H$46,””)
11. (35:03) IF Example #10: Is it a Number?: =ISNUMBER(C7)
12. (38:26) IF Example #11: Is cell empty?: =ISBLANK(C7)
13. (39:09) IF Example #12: Is it Text?: =ISTEXT(C7)
14. (40:23) IF Example #13: Checkbook Balance: =IF(ISBLANK(B5),””,G4-F5+E5)
15. (48:22) IF Example #14: Sales Category: =IF(L7 Greater Than $O$7,”Large”,”Small”) and then make a PivotTable
16. (52:50) Summary – Conclusion
Mr Excel & excelisfun Trick 188: Sales Team Region Report: 5 Different Amazing Methods
Download workbook: : http://ift.tt/2zdkYFt
and
http://ift.tt/2hcNeB0
Bill Mr Excel Jelen and Mike excelisfun Girvin show how to use
Topics:
1. (00:11) Introduction
2. (00:31) Method 1: Power Pivot Data Model & Create PivotTable dialog box with Data Model PivotTable
3. (02:34) Method 2: Array Formula with SUMIFS and SUMPRODUCT
4. (06:42) Method 3: VLOOKUP and Standard PivotTable
5. (08:10) Method 4: Power Pivot Data Model & Relationships with Data Model PivotTable
6. (11:05) Method 5: Power Query with Pivot, Merge and Group By features
7. (14:37) Wrap up and conclusion
Excel Duel, Dueling Excel, Excel Duel 188
Excel Basics 12: Complete Formula Lesson of Formula Types & Formula Elements 12 Examples
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2iZNLH6
Download Full Written Notes: http://ift.tt/2z9Fzrs
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (00:30) Different Types of Formulas
3. (02:07) Formula Elements – what you can out into a formula.
4. (04:55) Formula 01: Goal: Add calls made a service center last week.: =SUM(C23:C29) . Number Formula.
5. (05:53) Formula 02: Goal: Calculate Monthly Insurance Expense.: =C37/12. Number Formula.
6. (06:25) Formula 03: Goal: Calculate Deduction for Each Employee.: =ROUND(C46*$F$46,2) . Number Formula.
7. (09:31) Formula 04: Goal: Calculate Cost of Goods Sold (COGS) in Accounting: =(C58-D58)*E58. Number Formula.
8. (13:17) Formula 05: Goal: Join “Text ” and an Item Number into One Cell.: =”Item # “&C70. Text Formula.
9. (17:02) Formula 06: Goal: Join First & Last Names into One Cell.: =C82&”, “&B82. Text Formula.
10. (20:43) Formula 07: Goal: Extract State Abbreviation using the RIGHT Function: =RIGHT(B96,2) . Text Formula.
11. (24:01) Formula 08: Goal: Extract Product Number using the LEFT Function: =LEFT(B108,6) . Text Formula.
12. (26:00) Formula 09: Goal: Determine If Debits = Credits: =B127=C127. Logical Formula.
13. (30:00) Formula 10: Goal: Determine If Employee Gets a Bonus: =C135 Greater Than =$F$135. Logical Formula.
14. (33:35) Formula 11: Goal: Count how many of each product we sold: =COUNTIFS($B$148:$B$153,B156). Number Formula.
15. (35:15) Formula 12: Goal: Formula to determine whether we need to reorder?: =$D$173 Greater Than B166-C166. Logical Formula.
16. (41:00) Formula 13: Goal: Calculate Net Income: =C181-SUM(C182:C185) . Number Formula.
17. (42:30) Summary – Conclusion