Excel Magic Trick 1488: Vote For Favorite Formula to Lookup Correct Price Based on Effective Date

Click on Link to vote for your favorite formula and try and win a free Excel Array Formula book written by Mike “excelisfun” Girvin.

Vote Link: https://ift.tt/2JAA7Cf

5 Video playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0kFLauAJprHUNmRWafIUb4D
Download Excel File: https://ift.tt/2JU1pnY
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs

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

Excel Magic Trick 1487: INDEX, MATCH & MAXIFS : Lookup Correct Price Based on Effective Date

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 INDEX, MATCH and MAXIFS functions in an Array Formula.

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

Vote for your favorite formula, here: https://ift.tt/2JAA7Cf

View on YouTube

Excel Magic Trick 1486: VLOOKUP, MAXIFS, & Key: Lookup Correct Price Based on Effective Date

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

Excel Magic Trick 1485: SUMIFS & MAXIFS Functions: Lookup Correct Price Based on Effective Date

Download Files: Start 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 SUMIFS & MAXIFS functions.

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

Excel Magic Trick 1484: LOOKUP & Sorted Helper Column: Lookup Price Based on Effective Date

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

Excel Magic Trick 1483: LOOKUP Array Formula: Lookup Correct Price Based on Effective Date

Download Files: Start 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 LOOKUP Function in an Array Formula.

Other Related Videos:
Excel Magic Trick 1483: LOOKUP Array Formula: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1484: VLOOKUP & OFFSET: Lookup Correct 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

Excel & Business Math 45: Future Value, Present Value and Periodic Payments for Annuities

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

Excel & Business Math 44: What is Present Value? Calculate Present Value Of Lump Sum Amount.

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

In this video learn what Present Value is and how to calculate the Preset Value of a Lump Sum Future Value Amount.
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:15) What is Present Value?
3. (07:26) Example 2: Present Value Calculation is the Reverse of the Future Value Calculation.
4. (11:14) Example3: How Much do I have to put in the Bank to become a millionaire.
5. (15:06) Example 4: How much to deposit today to have money for my daughter’s College.
6. (17:38) Example 5: Learn How the Time Variable (Total Number of Compounding periods) is the most significant variable for earning Compound Interest.
7. (20:54) Fundamental Truth In Finance
8. (22:21) Summary

Next Video: 45 Excel & Business Math

View on YouTube

Excel & Business Math 43: Compound Interest Calculating Future Value of a Lump Sum FV Excel Function

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

Free Excel Education for the World at the excelisfun Channel at YouTube!

Excelisfun YouTube Channel: https://www.youtube.com/user/ExcelIsFun
Excel Basics Free Class Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
Advanced Excel Free Class Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw
Excel & Business Math Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0kMy6GdNvsecPSOP5Tc_ydL
Dashboard Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0kYt4c50Sg7BXGLBAjiW6VQ
Power Query Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK
VLOOKUP Function Playlist:
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…

View on YouTube

Excel Magic Trick 1482: SUMPRODUCT, DCOUNTA or SUM & IF for Counting with OR Logical Test

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