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 this 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.