## Basic Formulas **Index, Match, and Lookups**

This course covers Basic Formulas and consists of 36 topics.

Basic Formulas Part One –

**This video explains how to copy a formula not involving “$” signs.**Basic Formulas Part Two –

**This video explains how to copy formulas across rows and down columns, the meaning of $ signs, and setting up a dynamic model of customer evolution.**Basic Formulas Part Three –

**This video explains how to create a worksheet that computes students final grades based on homework and exams.**Computing a Production Function –

**In this video we show how to compute a production function which shows how labor and cloth can be transformed into dresses.**Diving Score –

**This video explains using the LARGE function to help compute Olympic Diving Scores.**Gini Index –

**This video shows how to use Excel to compute a complex important formula (Gini index measuring income inequality). It also discusses Order of Operations (PEMDAS).**Body Mass Index –

**This video shows how to use PEMDAS to compute Body Mass Index (BMI).**Big Mac Index –

**In t****his video we use the Economist’s Big Mac Index and basic spreadsheet formulas to show that the Chinese currency is vastly undervalued.**Modeling Supply and Demand –

**This video explains how to create a simple chart to model supply and demand for a product.**Cost Curves –

**This video explains how to use PEMDAS to compute basic economic cost curves (Average Cost, Marginal Cost, etc.).**Retirement –

**This video explains how to model the evolution of a retirement fund.**Modeling Fish in a Pond –

**This video explains how to create a dynamic model that shows the evolution of the number of fish in a pond.**Amortization Schedule –

**This video explains how a loan payment reduces the balance of a loan by the end of the loan term.**Depreciation –

**This video shows how to model 3 different methods of depreciation used to depreciate a machine to its final salvage value.**Gravity Model –

**This video explains how to implement a complex formula, the Gravity Model which computes market shares for stores based on their locations and quality.**Butterfly Effect –

**This video illustrates the Butterfly effect: If a butterfly flaps its wings in Tahiti it may cause a snowstorm in Kansas.**Product Line Pricing –

**This video shows how changing the price of two products, (specialty and regular coffee) changes the demand for each product.**Variance Analysis –

**In this video we show how to decompose a sales variance into a volume, product mix, and price variance.**Vertical Lookup –

**This video shows how to use the VLOOKUP function to compute a tax rate based on income and a products price based on its code.**Horizontal Lookup –

**This video uses the HLOOKUP function to lookup sales and employees during each month.**Product Prices Based on Date –

**This video uses a VLOOKUP to determine a product’s price based on a date when the price changes over time.**Vertical Lookup From Multiple Worksheets –

**This video shows how to use a VLOOKUP to efficiently pull multiple columns of data from different worksheets.**Vertical Lookup From Multiple Columns –

**This video shows how to efficiently pull multiple columns of data using a VLOOKUP and the COPY command.**Index Function –

**This video introduces the INDEX function.**Index With Rows and Columns –

**This video shows how to use the INDEX function to pick off an entire row or column.**Match Function –

**This video defines the MATCH function and explains the three values (-1,0 and, +1) that are related to the lookup type.**Combining Match and Index –

**This video shows how to combine the MATCH and INDEX functions to lookup the distance between two cities based on their names.**Match, Index, and Large Functions –

**This video shows how to use the MATCH, INDEX, and LARGE functions to find the country with the n’th largest population.**Finding the Most Recent Date of a Sale –

**This video shows how to use MATCH lookup type +1 to find the date of the most recent sale.**Cost of Living –

**In this video we answer the question: Is a $140,000 salary in New York better than a $110,000 salary in West Virginia?**Currency Exchange –

**In this video we create a calculator to determine the exchange rate between any two currencies.**Predicting the Future of the US Population –

**In this video we show how to use the copy command and the vlookup function to help us predict the future age distribution of the US population.**The CHOOSE Function –

**In this video we show how the CHOOSE function can be used as an alternative to Lookup formulas.**VLOOKUPS and Income Taxes –

**In this video we use 3 VLOOKUPS to compute the amount of federal taxes owed for any amount of after-tax income.**Math is Fun –

**In this video we use Excel to illustrate three beautiful results from elementary mathematics.**Rule of 72 –

**In this video we show how the Rule of 72 allows you to easily approximate for a given annual interest rate, the number of years needed to double your money.**