Download Files: Start Excel File: http://ift.tt/2pj6AUR
Entire page with all Excel Files for All Videos: http://ift.tt/1zHY6GI about how to
In this video learn how to do a two-way lookup to get the correct Price based on Quantity Discount & Product Name. Learn how to use the VLOOKUP and MATCH Functions.
Tag: How to
Welcome to the excelisfun Channel at YouTube
Everything you need is here at the excelisfun Channel at YouTube:
1. Search for topics
2. Free College Classes
3. Playlists for Your Excel Topics
4. Excel Basics
5. Charts
6. PivotTables
7. Array Formulas
8. Power Query
9. Power Pivot
10. Power BI Desktop
11. Efficiency
12. Compare and Contrast Methods
13. Much more…
Click to go to excelisfun channel: https://www.youtube.com/user/ExcelIsFun
Click to go to Highline College: http://ift.tt/2GJJOhr
Excel Magic Trick 1479: Lookup Bulk Quantity Discount Prices Using VLOOKUP Function
Download Files: Start Excel File: http://ift.tt/2pj6AUR
Entire page with all Excel Files for All Videos: http://ift.tt/1zHY6GI about how to
In this video learn the Basics of VLOOKUP to looks up a price based on a Bulk Quantity Discount. Also learn about the MATCH Function to automatically pick out the correct Column Number for the VLOOKUP Function.
1. (00:01) Introduction
2. (00:14) Use VLOOKUP and Column Index Number to lookup a price based on a Quantity Discount
3. (05:14) Use VLOOKUP and MATCH to lookup a price based on a Quantity Discount
4. (09:44) Summary
Excel & Business Math 41: Markup On Cost or Markup On Sell Price? Calculate & How They Are Different
Download Start Excel File: http://ift.tt/2HE7bbw
Download pdf Notes: http://ift.tt/2pfiM9V
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn the difference between Markup on Cost and Markup on Sell Price.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (00:52) When Thinking About Markup, There Are Two Sides: Cost Side & Sell Price Side
3. (03:01) Both the Cost Side (Manufacturers & Distributors) & Sell Price Side (Retail Stores) Calculate the $ Markup the Same
4. (05:33) Learn Formulas For “% Markup”. Cost Side (Manufacturers & Distributors) uses Cost As Denominator and Sell Price Side (Retail Stores) uses Sell Price As Denominator
5. (12:27) Meaning of “% Markup On Cost” and “% Markup On Sell Price”
6. (13:25) Solve Markup on Cost Problem
7. (16:45) Solve Markup on Sell Price Problem
8. (19:25) Solve Both Markup on Cost & Markup on Sell Price Problems
9. (23:15) Summary
Next Video: 42 Excel & Business Math
Excel & Business Math 40: Partial Payment on Invoice with Cash Discount, Credit to Account Balance?
Download Start Excel File: http://ift.tt/2FBj6dw
Download pdf Notes: http://ift.tt/2Hwaql9
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn how to calculate the credit that should be applied a the balance of an account when a partial payment in made with a cash discount applied.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (01:06) Partial Payment on Invoice with Cash Discount Applied Video Example #1, Common Sense Method
3. (05:23) Partial Payment on Invoice with Cash Discount Applied Video Example #1, Increase Decrease Method
4. (08:53) Partial Payment on Invoice with Cash Discount Applied Video Example #2, Increase Decrease Method
5. (10:42) Summary
Next Video: 41 Excel & Business Math
Calculate Partial Payment with Cash Discount
Excel & Business Math 39: Create Excel Invoices, Data Validation Drop-down, VLOOKUP & IF Functions
Download Start Excel File: http://ift.tt/2pciCj0
Download pdf Notes: http://ift.tt/2Iohn9b
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn how to create an Invoice that you can use over and over using Data Validation Drop-down, VLOOKUP, ISTEXT, ISNUMBER & IF Functions. Also see how to create a formula to extract Invoice Number from Sheet name using the CELL, REPLACE, SEARCH and LEFT Functions.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction to creating an invoice in Excel
2. (01:30) Format Invoice
3. (02:32) Insert Company Logo and Boomerang Picture
4. (06:01) Calculate Wholesale Cost using a Single Trade Discount
5. (08:30) Data Validation – List to insert Drop-down list of Products
6. (11:29) Exact Match VLOOKUP to get price
7. (13:06) IF and ISTEXT to turn formula off when no product entered
8. (17:01) IF & ISNUMBER for Line Item Total
9. (18:41) SUM Function and other formulas for totals
10. (20:18) Page Setup
11. (21:44) Copy a Sheet using Mouse & Ctrl Key
12. (24:57) CELL, SEARCH, REPLACE and LEFT Functions to get Invoice Number
13. (29:25) Summary
Next Video: 40 Excel & Business Math
Excel & Business Math 38: Date Math, Invoices, Cash Discounts on Invoices, Nested IF Functions
Download Start Excel File: http://ift.tt/2FEK4jJ
Download pdf Notes: http://ift.tt/2Hn20MW
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn how to
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (00:48) Enter Dates in Excel
3. (03:19) Date Number Formatting
4. (05:44) Date Math, Date Formulas and Date Functions like EDATE and EOMONTH
5. (15:18) Invoices, Different Elements
6. (17:11) Shipping Terms, including FOB Shipping and FOB Destination
7. (19:40) When Tax is Paid, including selling final item at a whole sale cost
8. (20:58) Cash Discounts: Incentive to Pay Invoice Early
9. (22:44) Ordinary, Cash Discounts Example 1. First Example and Close look at what Terms Mean.
10. (33:04) Ordinary, Cash Discounts Example 2
11. (38:53) Ordinary, Cash Discounts Example 3. This example shows what to do if you have two discount opportunities, including how to use Two IF Functions in a single formula, known as Nesting If Functions.
12. (48:50) AS OF, Cash Discounts
13. (49:40) ROG, Cash Discounts
14. (51:10) EOM, Cash Discounts. Including the EOMONTH Function and how to use a Logical Value (TRUE / FALSE) value in a formula as a 0 or 1.
15. (01:02:42) Summary
Next Video: 39 Excel & Business Math
Excel & Business Math 37: Trade Discounts, Series Discounts, Net Cost Equivalents & Wholesale Cost
Download Start Excel File: http://ift.tt/2FqXHDO
Download pdf Notes: http://ift.tt/2tvXcmg
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn how to calculate Trade Discounts (Single and Series) in order to calculate a Wholesale Cost. Learn about Business Terms. Learn about using VLOOKUP and IF Functions to automat calculations.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (01:27) Business Terms: Retail & Wholesale Businesses. List Price and Wholesale Cost. Trade Discount $ Amount.
3. (05:42) Examples of Wholesale Cost Catalogs and the four different formulas needed to calculate Whole Sale Costs under four different assumptions.
4. (11:28) Single Trade Discount % Video Example. See three different Methods to calculate Wholesale Cost.
5. (15:02) Calculate Series Discounts. See three different Methods to calculate Wholesale Cost.
6. (20:28) Series Trade Discount % with Two Successive Discounts
7. (21:42) Series Trade Discount % where 2nd Discount Depends on Quantity Purchased. Learn how to use VLOOKUP Function.
8. (26:23) Series Trade Discount % where 2nd & 3rd Discount Depends on Quantity Purchased.
9. (30:43) Learn how to use IF & VLOOKUP Functions to make calculations automatic for a Series Discount.
10. (34:57) Summary
Next Video: 38 Excel & Business Math
Excel & Business Math 36: Comprehensive Excel Payroll Example: SUMIFS, MROUND, MOD, VLOOKUP & More
Download Start Excel File: http://ift.tt/2oINXK0
Download pdf Notes: http://ift.tt/2HXlx7M
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn how to create a complete payroll table solution based on the topics learned in Videos #29 to 35. Learn about the SUMIFS Function for the first time.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (02:15) Formula for: Hours Worked, using the Functions: SUM, MOD, MROUND
3. (06:42) Formula for: Total Hours, using the Function: SUMIFS
4. (11:25) Formula for: Regular Hours, using the Function: IF
5. (13:26) Formula for: Overtime Hours
6. (13:48) Formula for: Regular Gross Pay, using the Function: ROUND
7. (14:24) Formula for: Overtime Gross Pay, using the Functions: ROUND
8. (14:57) Formula for: Total Gross Pay, using the Functions: SUM
9. (15:16) Hide Columns
10. (15:56) Formula for: Cumulative Gross Pay After This Paycheck
11. (16:24) Formula for: Social Security Deduction, using the Functions: MEDIAN, ROUND
12. (18:42) Formula for: Medicare Deduction @ 1.45%, using the Functions: MEDIAN, ROUND
13. (20:09) Formula for: Medicare Deduction @ 2.35%, using the Functions: MEDIAN, ROUND
14. (22:30) Formula for: Taxable Pay, using the Function: MAX
15. (23:30) Formula for: Federal Income Tax Withholding, using the Functions: VLOOKUP, ROUND
16. (26:03) Formula for: Total Deductions
17. (26:30) Formula for: Net Pay
18. (26:44) Summary
Next Video: 37 Excel & Business Math
Excel & Business Math 35: VLOOKUP & MAX Functions for Percentage Method Federal Income Tax Deduction
Download Start Excel File: http://ift.tt/2F21S4D
Download pdf Notes: http://ift.tt/2owePNU
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn how to use VLOOKUP & MAX Functions to make Federal Income Tax Withholding Deduction formulas.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (00:31) Federal Income Tax Withholdings Deducted from Employees Paychecks
3. (02:42) IRS web site with 2018 Percentage Method Tax Tables.
4. (04:00) 2018 Percentage Method Federal Income Tax Withholding Tables in Excel
5. (06:19) Video Example 1: Calculate Federal Income Tax Withholdings for Marital Status “Single” and Pay Period Weekly. Calculations made by Hand and with MAX and VLOOKUP.
6.
7. (17:05) Use Formula Evaluator to verify that VLOOKUP Formula is working correctly
8. (18:38) Video Example 2: Calculate Federal Income Tax Withholdings for Marital Status “Married” and Pay Period Weekly. Calculations made by Hand and with MAX and VLOOKUP.
9. (21:36) Video Example 3: Calculate Federal Income Tax Withholdings for Marital Status “Single” and Pay Period Weekly for an Entire Payroll Table. Calculations made with MAX and VLOOKUP.
10. (27:33) Video Example 4: Calculate Federal Income Tax Withholdings for Marital Status “Married” and Pay Period Weekly for an Entire Payroll Table. Calculations made with MAX and VLOOKUP. See how to copy a Whole Table for formulas to a new template.
11. (28:28) Summary
Next Video: 36 Excel & Business Math
Excel & Business Math 34: MEDIAN Function for FICA Social Security & Medicare Payroll Deductions
Excel & Business Math 34: MEDIAN Function for FICA Social Security & Medicare Payroll Deductions
Download Start Excel File: http://ift.tt/2t0QtAv
Download pdf Notes: http://ift.tt/2Cr8I67
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn how to
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (01:28) FICA Payroll Social Security & Medicare Tax Deductions
3. (04:50) Calculating FICA Deductions and Net Pay Long Hand, all three situations
4. (17:32) Learning How to Make a Tax Calculation When There is a Hurdle That Changes The Tax Rate
5. (18:13) Using MEDIAN Function to Deal With Tax Hurdles
6. (31:17) Payroll Employee Table Example for Tax Deductions and Net Pay
7. (37:44) Summary
Next Video: 35 Excel & Business Math
Excel & Business Math 33: VLOOKUP Function for Incentive Pay: Commissions & Piecework (15 Examples)
Download Start Excel File: http://ift.tt/2HLKjrj
Download pdf Notes: http://ift.tt/2GJRY88
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn all about the VLOOKUP Function, How to Build Lookup Tables and all about Incentive Pay such as Piecework and Commission Pay.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (02:25) Incentive Pay such as: Straight Piecework, Variable Piecework, Straight Commission, Variable Commission Rates, Commission Rate Based on Amount of Sales
3. (06:42) Straight Piecework Example
4. (08:24) Variable Piecework Example Making the Calculations Long-Hand
5. (11:28) Why Excel is so Helpful AND why we must be REALLY Knowledgeable with Excel.
6. (12:00) Variable Piecework Example Making the Calculations with the “Lookup Table Method”
7. (20:04) Creating the Lookup Table from Scratch
8. (30:31) Variable Piecework Example Making the Calculations with the “Lookup Table Method & VLOOKUP Function using Approximate Match”
9. (40:19) Variable Piecework & VLOOKUP & Approximate Match Lookup for Entire Payroll Table:
10. (44:10) What is VLOOKUP Function?
11. (44:42) “Mash” all 3 VLOOKUP Functions into Single Formula using Clipboard:
12. (not in video look in pdf notes) VLOOKUP Function Arguments (Full Details):
13. (49:00) Lookup Tables are Everywhere
14. (51:55) Straight Commission Rates
15. (52:51) VLOOKUP & Exact Match Lookup for Straight Commission Rates When each Employee has Different Rate
16. (59:00) VLOOKUP & Approximate Match Lookup for Variable Commission Rates; Make Lookup Table and use VLOOKUP
17. (01:15:25) VLOOKUP & Approximate Match Lookup for Commission Rate Based on Amount of Sales Made by the Employee
18. (01:20:02) Summary
Next Video: 34 Excel & Business Math