Download Excel File: https://ift.tt/2JU1pnY
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video learn how to Lookup the correct product price based on the latest Effective Date. The Lookup table has multiple listings of the product, each with a different effective date and price. In this video learn about how to use the VLOOKUP and MAXIFS functions (also AGGREGATE Function) and a Helper Column (Key Column) in the Lookup Table.

Related Videos:
Excel Magic Trick 1483: LOOKUP Array Formula: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1484: LOOKUP & Sorted Helper Column: Lookup Price Based on Effective Date
Excel Magic Trick 1485: SUMIFS & MAXIFS Functions: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1486: VLOOKUP, MAXIFS, & Key: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1487: INDEX, MATCH & MAXIFS : Lookup Correct Price Based on Effective Date
Excel Magic Trick 1488: Vote For Favorite Formula to Lookup Correct Price Based on Effective Date

View on YouTube

Download Excel File: https://ift.tt/2JU1pnY
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video learn how to Lookup the correct product price based on the latest Effective Date. The Lookup table has multiple listings of the product, each with a different effective date and price. In this video learn about how to sort the lookup table by Effective Date within Product Column, then create a Helper Column (Key Column) as the first column in the lookup table and then use the & VLOOKUP & LOOKUP functions. Thanks to Enny Kraft at YouTube for this trick!

Related Videos:
Excel Magic Trick 1483: LOOKUP Array Formula: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1484: LOOKUP & Sorted Helper Column: Lookup Price Based on Effective Date
Excel Magic Trick 1485: SUMIFS & MAXIFS Functions: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1486: VLOOKUP, MAXIFS, & Key: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1487: INDEX, MATCH & MAXIFS : Lookup Correct Price Based on Effective Date
Excel Magic Trick 1488: Vote For Favorite Formula to Lookup Correct Price Based on Effective Date

View on YouTube

Download Start Excel File: https://ift.tt/2GXgWkR
Download pdf Notes: https://ift.tt/2HAshZp
Entire Class Web Site: https://ift.tt/2BjafuY

In this video learn how to make Present Value, Future Value and PMT for the Cash Flows, both Annuity Cash Flows and Irregular Cash Flows. This is a comprehensive video about Financial Products, Cash Flows and Annuities.
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:27) Cash Flow Pattern Diagram for Future Value and Irregular Cash Flows. Future Value Calculation of Savings Plan with Irregular Cash Flows, Hand Drawings & Diagram.
3. (04:34) Excel Example 1: Calculate Future Value of Savings Plan with Irregular Cash Flows.
4. (09:17) Cash Flow Pattern Diagram for Present Value Calculation of Irregular Future Cash Flows. Asset Valuation Calculation. Hand Drawings & Diagram.
5. (11:51) Excel Example 2: Calculate Present Value of Irregular Future Cash Flows to determine Asset Valuation.
6. (19:35) Define Terms for an Annuity
7. (21:00) Cash Flow Pattern Diagram for Future Value and Present Value of Periodic Cash Flows
8. (23:14) Excel Example 3: Calculate Future Value for Savings Plan with Periodic Cash Flow in an End Annuity. FV Function.
9. (32:02) Excel Example 4: Calculate Present Value of Future Periodic Cash Flows (End Annuity) to determine Asset Valuation. PV Function.
10. (37:45) Excel Example 5: Calculate PMT When Present Value Amount is Known. How Much Can We Withdraw at End of Each Month for Next 30 Years? PMT Function.
11. (42:17) Excel Example 6: Calculate PMT When Future Value Amount is Known. How Much Should I Deposit at the End of Each Month to Become Millionaire? PMT Function.
12. (45:47) Excel Example 7: Calculate PMT For a Home Mortgage Loan, Where Loan is Positive Present Value Amount. PMT Function.
13. (48:33) Summary

Link to full Finance Class: https://www.youtube.com/playlist?list=PL90E1F26C7B85E78F

View on YouTube

Download Start Excel File: https://ift.tt/2GJNDCr
Download pdf Notes: https://ift.tt/2HjmbMI
Entire Class Web Site: https://ift.tt/2BjafuY

In this video learn how to calculate the Future Value of an Lump Sum Investment or Debt Instrument using the Excel FV Function and the Future Value Math Formula.
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:05) Compare Simple Interest and Compounded Interest Long Hand on Paper
3. (05:00) Example 01 & 02: Simple Interest Compared to Compound Interest In Excel & Two Sides to Every Debt Transaction
4. (13:51) New Terms for Compound Interest – we will use these for the rest of the Finance Videos.
5. (20:35) Direction of the Cash Flow Matters: For Compound Interest Calculations in Excel, the Direction of the Cash Flow Matters
6. (23:28) Example 03: Math Formulas and FV Excel Function in Excel to make Future Value Calculations, both long hand and in a single cell.
7. (32:32) Video Example #04 for Future Value Calculation for Investment (Savings Account with quarterly Interest Payments)
8. (36:03) Video Example #05 for Future Value Calculation for Debt
9. (38:27) Video Example #06: Comparing Simple Interest and Compound Interest in a Graph
10. (44:04) Video Example #07 for Future Value Calculation for Savings Account (Savings Account with Daily Interest Payments)
11. (47:00) Video Example #08 for Future Value Calculation for Savings Account (Savings Account with quarterly Interest Payments AND we leave amount in Bank for 35 years!)
12. (49:17) Daily Savings Account Example
13. (51:10) Daily Savings Account Examples for less than one year. We calculate Total Number Of Periods using Date Math.
14. (53:25) Summary

Next Video: 44 Excel & Business Math

View on YouTube

Download Files: Start Excel File: https://ift.tt/2q6caLN
Entire page with all Excel Files for All Videos: https://ift.tt/1zHY6GI about how to
In this video learn how to Count with an OR Logical Test based on Three Columns. See a Pharmacy Data Set with Patient Data with the goal of counting how many patients are eligible for the service based on an OR Logical Test. The difficultly in this calculation is that we need to avoid Double Counting. See three formulas: 1) SUM and IF Function Array Formula, which requires Ctrl + Shift + Enter, 2) SUMPRODUCT Array Formula, which does not Require Ctrl + Shift + Enter, and 3) DCOUNTA database function, which is not an Array Formula but has an unusual Criteria Requirement.
Learn all about Array Formulas, what they are and how to create them.
Topics:
1. (00:01) Introduction & Discussion of pitfalls of OR Logical Tests when there is the chance of Double Counting
2. (03:05) Formula #1: SUM and IF Function Array Formula, which requires Ctrl + Shift + Enter
3. Formula #2: SUMPRODUCT Array Formula, which does not Require Ctrl + Shift + Enter
4. Formula #3: DCOUNTA database function, which is not an Array Formula but has an unusual Criteria Requirement.
5. Summary

View on YouTube

Download Files: Start Excel File: https://ift.tt/2pj6AUR
Entire page with all Excel Files for All Videos: https://ift.tt/1zHY6GI about how to
In this video learn how to how to perform lookup from multiple tables. Learn two methods: 1) VLOOKUP and SWITCH Function or 2) VLOOKUP and INDIRECT Function with Defined Names.
1) (00:01) Introduction
2) (01:30) VLOOKUP and SWITCH Function (New Excel 2016 Office 365 Function)
3) (07:20) VLOOKUP and INDIRECT Function with Defined Names.
4) (11:06) Summary

View on YouTube