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

This chapter covers Basic Formulas and consists of 36 topics.

**This video explains how to copy a formula not involving “$” signs.**

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

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

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

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

**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).**

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

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

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

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

**This video explains how to model the evolution of a retirement fund.**

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

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

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

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

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

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

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

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

**This video uses the HLOOKUP function to lookup sales and employees during each month.**

**This video uses a VLOOKUP to determine a product’s price based on a date when the price changes over time.**

**This video shows how to use a VLOOKUP to efficiently pull multiple columns of data from different worksheets.**

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

**This video introduces the INDEX function.**

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

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

**This video shows how to combine the MATCH and INDEX functions to lookup the distance between two cities based on their names.**

**This video shows how to use the MATCH, INDEX, and LARGE functions to find the country with the n’th largest population.**

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

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

**In this video we create a calculator to determine the exchange rate between any two currencies.**

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

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

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

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

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

## Range **Names**

This chapter covers Range Names and consists of 9 topics.

**This video shows how to create range names using the Name Box.**

**This video shows how to create range names based on spreadsheet text using Create from Selection and using the Name Manager to edit and delete range names.**

**This video shows how to use the F3 key to paste a range name into a formula.**

**This video shows how to name an entire row or column.**

**This video shows how to ensure that if you create range names, they will show up in previously created formulas.**

**This video shows how you can create range names that apply to only a single worksheet or the entire workbook.**

**This video shows how to use a named row in a formula and create ranges based on Excel formulas.**

**This video shows how to pick off an entry in an array by simply typing the row and column headings.**

**This video shows how to use Range names in VLOOKUP formulas.**

## Text **Functions**

This chapter covers Text Functions and consists of 13 topics.

**This video shows how to use the LEFT, RIGHT, MID, LEN and CONCATENATE functions to manipulate text in a single cell.**

**This video shows how to use the FIND function and TEXT to COLUMNS to parse data in a single cell.**

**This video shows how to use the REPT function to create a simple histogram.**

**This video shows how to Use the CLEAN, FIND, and SUBSTITUTE functions to remove invisible characters from your data.**

**This video shows how to use FLASH FILL in Excel 2013 and 2016 to automatically perform the work of text functions.**

**In this video we show how Control + E makes Flash Fill perform better.**

**This video shows how to use the SUBSTITUTE function to find the last word in a cell.**

**This video shows how to use a VLOOKUP based on multiple columns of data.**

**This video shows how to turn Social Security Numbers into PeopleSoft IDs.**

**This video shows how to use Excel’s vast library of UNICODE characters.**

**This video shows how to take a list of email aliases and turn it into an email list.**

**In this video we show how the TEXT function can ensure that a value in one cell is converted in a different cell to any desired number format.**

**In this video we show how to use the ROUND, ROUNDUP, ROUNDDOWN, and INT functions to round numbers in any desired fashion.**

## Date and **Financial Functions**

This chapter covers Date and Financial Functions and consists of 21 topics.

**This video shows how to use the YEAR, DAY, WEEKDAY, DATE, WORKDAY, and NETWORKDAYS functions to manipulate dates.**

**This video shows how to use the DATEDIF function to find years or months between two dates.**

**This video shows how to insert a Static Date (a date that does not change) into a spreadsheet.**

**This video shows how to create a Custom List (like all NFL teams) that you can drag to Autofill.**

**This video shows how to find the date of Martin Luther King Day for a given year.**

**This video shows how to use the NPV function to find the present value of a sequence of cash flows.**

**This video shows how to use the XNPV function to find the present value of irregularly spaced cash flows.**

**This video shows how to use the IRR function to find the Internal Rate of Return of a sequence of cash flows.**

**This video shows how to use the XIRR function to find the IRR of irregularly spaced cash flows and using the MIRR function to find the Modified Internal Rate of Return.**

**In this video we discuss the MIRR Function which improves on the IRR function.**

**This video shows how to use the PV function to evaluate an annuity and the FV function to evaluate the accumulation of money for retirement.**

**This video shows how the FVSCHEDULE function is used to find the future value when interest rates vary over time**

**This video shows how to use the PMT, IPMT, PPMT, CUMPRINC, and CUMIPMT functions to analyze loans and mortgages.**

**This video shows how to use the RATE function to find the maximum interest rate a borrower can tolerate and using the NPER function to determine the number of periods needed to pay off a loan given the size and annual rate for the loan.**

**In this video we calculate the fraction of our loan size that we pay to the bank each year**.

**In this video we use Excel financial functions to determine a fair price for a bond.**

**In this video we use Excel’s financial functions to explain the relationship between loan expenses and a loan’s APR**

**In this video we demonstrate Excel functions that make it easy to compute period by period depreciation.**

**In this video we show how Excel’s financial functions can be used to solve problems involving compound interest.**

**In this video we show how to use Excel’s financial functions to value a growing annuity.**

**In this video se show how Excel’s financial functions can be used to compute payments on an Adjustable Rate Mortgage.**

## Auditing Tools, Circular References, and **IF Functions**

This chapter covers Auditing Tools, Circular References, and IF Functions and consists of 23 topics.

**This video shows how to use Precedents to see which cells are needed to compute a formula and using Dependents to find cells that are dependent on spreadsheet inputs.**

**This video shows how circular references can be used and resolved.**

**This video shows how to use IF statements to calculate purchasing costs and analyze the game of craps.**

**This video explains how to use IF statements and the MOD function to determine the winner in odds and evens.**

**This video shows how to use IF statements to extract annual sales from quarterly sales data.**

**This video shows how to use IF statements to model sales during the product life cycle.**

**This video shows how to use IF statements to extract the data in a 20 row by 12 column table into 240 different rows.**

**This video shows how to use the IFERROR function to remove error messages from your spreadsheets. It also covers the AGGREGATE function which allows you to ignore error values in calculations.**

**This video shows how to use the IFERROR function to copy a formula down many rows when every 10th row has headings and not data.**

**In**

**this video we show how to use the IF and ISERROR functions to adjust a professor’s number of students based on whether a course lasted half a semester.**

**This video shows how to determine if a check number like 2453589-6 has the sum of digits divisible by 7.**

**This video shows how to use a two stage growth model to reverse engineer a stock price.**

**This video shows how to efficiently put information for 1000 people in a single row if you have a person’s name in one row, street address in another row, and city in the next row.**

**This video shows how to use IF statements to compute sales commission based on a tiered commission structure.**

**In this video we use VLOOKUP, MATCH, INDEX, And IF functions to model a complex sales incentive plan.**

**This video shows how IF statements can be used to model investment strategies involving options.**

**In this video we use IF statements to model customer value, insurance premiums, and a person’s future salary.**

**In this video we show how ASCII characters, IF, IFERROR, VLOOKUP, MATCH, and INDEX Functions can be used to implement Julius Caesar’s famous basic shift code.**

**In this video we show how to use IF Statments and Text Functions to Implement the Famous Rail Fence Code.**

**In this video we use IF statements to find the longest winning streak by the Washington Nationals during the 2016 season.**

**In this video we show how to use IF statements and the MATCH function to study the Ducci sequence**

**In this video we use IF statements, the ISEVEN function, and the INDEX function to model the amazing Stern Sequence**

**In this video we use IF statements and Range Names to show how interpolation can be used to determine how the ratio of your waist to height determines how many years (on average) you will live**

## Inquire **Add-In**

This chapter covers the new Inquire Add-In and consists of 7 topics.

**In this video we show how to install the Inquire Add-In and describe its capabilities.**

**In this video we show how to compare workbooks using the Inquire Add-In.**

**In this video we show how to analyze the key elements of a workbook.**

**In this video we show how to create a diagram that describes links between workbooks.**

**In this video we show how to create a diagram that describes links between workbooks and worksheets.**

**In this video we show how to create cool diagrams that extend Trace Precedents and Trace Dependents.**

**In this video we show how the Inquire Add-In can be used to clean excess formats from a workbook.**

## Data Tables, Goal Seek, and **Scenario Manager**

This chapter covers Data Tables, Goal Seek, and Scenario Manager and consists of 17 topics.

**This video shows how to use a One Way Data Table to determine how changing a single spreadsheet input affects multiple outputs.**

**This video shows how to use a Two way data table to determine how changes in two spreadsheet inputs affect a single output.**

**This video shows how to use a Two way data table to determine how the monthly payment on a loan depends on the loan size and annual rate.**

**This video uses a Two way data table to determine how a customer’s value depends on the time value of money and customer retention rate.**

**This video shows how Goal Seek can be used to determine the number of unit sales needed for a juice bar to break even.**

**This video shows how to use Goal Seek to determine the annual rate on a loan that makes the monthly payment hit a desired value.**

**This video shows how the Scenario Manager can be used to describe how scenarios involving multiple spreadsheet inputs influence multiple spreadsheet outputs.**

**This video shows how to use a Two way data table to determine how the return on equity for a home investment depends on the size of the down payment and the change in home value.**

**This video shows how to use a One-Way data table to determine the critical activities in a project.**

**This video shows how to use a One way data table to show how hedging with puts can reduce equity risk.**

**This video explains how to use a Two way data table to show that Belichick was right when he went for it on 4th down against the Colts.**

**In this video we combine IF statements and a one-way data table to calculate how many antitrust cases were active during the years 1960-2016.**

**In this video we show how to create a chart to summarize the numbers in a two-way data table.**

**In this video we use a one-way data table to show how a simple hedge fund strategy neutralizes market risk.**

**In this video we use IF, MATCH, the ISEVEN function, and data tables to describe the famous Syracuse Problem conjecture**

**In this video we use data tables to show the relationship between bond prices and yields.**

**In this video we show how to create data table based on a model in a different worksheet.**

## Conditional Counts **and Sums**

This chapter covers Conditional Counts and sums and consists of 18 topics.

**In this video we show how the COUNTIF and COUNTIFS functions are used to do conditional counts on single or multiple criteria.**

**In this video we show how COUNT, COUNTA, and COUNTBLANK functions are used to count cells in a range containing numbers, non-blanks, or blanks.**

**This video shows how the SUMIF and AVERAGEIF functions are used to calculate conditional sums and averages based on a single criterion. It also shows how the SUMIFS and AVERAGEIFS functions are used to calculate conditional sums and averaged based on multiple criteria.**

**This video shows how the SUMIFS function and the Remove Duplicates functionality are used to calculate the sales each sales person made of each product.**

**This video shows how SUBTOTALS are used to determine how much each salesperson sold in each region.**

**This video shows how DATA CONSOLIDATE is used to combine data from several workbooks to calculate total sales of each product during each month.**

**This video shows how the DCOUNT, DSUM, and DAVERAGE functions are used to compute conditional counts, sums, and averages. It includes doing conditional calculations based on an “OR” criteria.**

**This video shows how Database functions are used with criteria based on an Excel formula.**

**This video shows how to use the DGET function to pull the location of a transaction based on the date and ID code.**

**This video shows how the Advanced Filter can be used to extract any subset of a spreadsheet’s data and copy this data to a new location.**

**This video shows how to do conditional sums and counts where one criteria is that the cell does not contain a blank.**

**In this video we show (amazingly) that for 70% of all countries the first digit in their population is 1,2,3, or 4!**

**In this video we show how to find the n’th match to a word in a column.**

**In this video we show how to use text functions, IFERROR, COUNTIF, and AVERAGEIF functions to analyze the effectiveness of Adrian Peterson’s off tackle runs.**

**In this video we show how to use the amazing SUMPRODUCT function to calculate conditional counts and sums**

**Usually conditional counts, sums, and averages utilize AND criteria. In this video we show how to utilize OR criteria with these functions.**

**In this video we show how to use the amazing SUMPRODUCT function to calculate conditional counts and sums.**

**In this video we use the SUMIFS functions to compute sales of all products at each of 221 beauty stores for a specified range of dates.**

## Conditional Formatting and **Data Validation**

This chapter covers Conditional Formatting and Data Validation and consists of 13 topics.

**This video shows how to highlight the 20 warmest temperatures since 1881 and all above average and below average temperatures since 1881.**

**This video shows how to highlight duplicates, any cells containing a given text string, and highlighting cells based on criteria involving dates.**

**This video shows how Data Bars, Color Scales, and Icon sets are used to summarize numerical data.**

**This video shows how cells can be formatted based on a complex Excel formulas. For example, highlight in green, each quarter a company’s sales increase, and in red each quarter a company’s sales decrease.**

**This video continues to show how to conditional format based on a formula.**

**This video shows how to use the Stop If True Criteria to highlight 10 states that have the highest income with an up arrow and not highlight any other states.**

**This video illustrates how Data Validation can return an error message when incorrect data is entered. For example, if we enter that an NBA team scored 1000 points, we receive an error message. We also show how to create a drop-down list from which a user selects an input.**

**This video shows how Data Validation criteria can be set up with Excel formulas. For example, we show how to ensure that entering any non-numerical entry in column A creates an error message.**

**In this video we show how to use data validation to prevent users from entering duplicate rows in a workbook**

**This video shows how to ensure that data not meeting a desired criteria is circled.**

**This video shows how to highlight employees who have just been hired.**

**In this video we show how to highlight all baseball players who are good on HR’s, Stolen Bases, and Batting Average.**

**In this video we show how to use data bars, color scales, and icon sets to format sports data.**

**Tables**

This chapter covers Tables and consists of 11 topics.

**This video shows that if you need a worksheet for each state set up in an identical format, how to easily use worksheet templates to set up the 50 needed worksheets.**

**This video shows how three dimensional formulas can be used to sum data across worksheets.**

**This video shows how the Paste Special Menu can be used to transpose data and divide “spread out cells” in a worksheet by 100**

**This video shows how making data a Table ensures that formulas update automatically when new data is added.**

**In this video we show how making data a Table ensures that charts update automatically when new data is added.**

**In this video we show how slicers can be used to “slice and dice” the data in Tables.**

**In this video we show how tables make drop down boxes, VLOOKUPS, SUMIF formulas and NPV calculations automatically update when new data is added.**

**In this video we show how the AGGREGATE function is needed to ensure calculations based on Table Slicers are correct.**

**In this video we show how to make Conditional Formats based on Formulas update automatically when new data is added.**

**This video shows how copying formulas across a table can be tricky.**

**In**

**this video we use VLOOKUP, ROW(), CHAR, MOD functions and the Table feature to encode and decode messages using the Vignere Code.**

## Pivot **Tables**

This chapter covers Pivot Tables and consists of 26 topics.

**In this video we introduce PivotTables and show how to collapse and expand fields, sort data, and drill down to source data.**

**In this video we show how to ensure that a PivotTable updates automatically when new data is added and how to use Value Filters to determine customers that generate 80% of our sales.**

**In this video we show how text filters are used to filter a PivotTable based on text.**

**In this video we show how to group data in a PivotTable and create a chart based on a PivotTable.**

**n this video we show how to summarize data as a Percentage of a Row or Column.**

**In this video we show how to create a calculated field based on PivotTable columns.**

**In this video we show how slicers allow you to slice and dice sales by country and product.**

**In this video we show how GETPIVOTDATA is used to extract data from a PivotTable for use in reports and charts.**

**In this video we show how to create a calculated item based on rows in a PivotTable. For example, group together all cars made in Germany in a Calculated Item called Germany.**

**In this video we show how to slice and dice PivotTable calculations based on any subset of months, quarters or years. The functionality of this video is for Excel 2013 and Excel 2016 users.**

**In this video we show how to summarize a survey with a PivotTable and PivotChart.**

**In this video we show how to create PivotTables from data in multiple worksheets and/or workbooks.**

**In this video we show how to create many PivotTables based on a Filter.**

**In this video we show how Excel 2013 and 2016 users can use the Data Model to efficiently link data without using thousands of VLOOKUPS.**

**In this video we show how to use Pivot Tables to analyze NBA players.**

**In this video we continue to show how Pivot Tables can be used to analyze NBA players.**

**In this video we show that the lack of C sections on weekends explains why there are fewer births on weekends.**

**In this video we use PivotTables to show why Democrats get more votes for Congress yet more Republicans win.**

**In this video we show how to use PivotTables to analyze the dating preferences of men and women.**

**In this video we show how PivotTables illustrate the classical Simpson’s Paradox.**

**In this video we show how to use IF, VLOOKUP, PivotTables, and AVERAGEIFS functions to solve a tax accounting problem.**

**In this video we use PivotTables and Slicers to analyze preferences of various demographic groups for Donald and Hillary.**

**In this video we show how subscribers to Office 365 can now easily update their Pivot Table defaults**

**In this video we show how slicers can be set to control more than one PivotTable**

**In this video we show how to apply Excel’s great Conditional Formatting capabilities to PivotTables**

**In this video we show how to group orders by month when Excel doesn’t like your date format.**

## Descriptive **Statistics**

This chapter covers Descriptive Statistics and consists of 12 topics.

**In this video we show how to use a histogram to summarize Cisco’s monthly returns.**

**In this video we discuss symmetric, positively, and negatively skewed histograms.**

**In this video we discuss measures of typical value, variation about the mean, and skewness.**

**In this video we discuss how to determine normal variation for any data set.**

**In this video we discuss the PERCENT, PERCENTRANK, LARGE, SMALL, and RANK functions.**

**In this video we show how to take a random sample size of 50 from 285 NBA players.**

**In this video we show how to fairly allocate members of the House of Representatives based on state populations.**

**In this video we show how statistical functions and data tables can be used to find your “perfect match.”**

**In this video we show how to choose a job based on multiple objectives.**

**In this video we use PivotTables, PivotCharts, and Excel Statistical functions to analyze the changes in the distribution of US family income between 2000 and 2015.**

**In this video we show how to compute the mean, variance, and standard deviation for grouped data.**

**In this video we use a histogram to prove that Chinese provinces under-report accidental deaths.**

## Trend Curve, Correlation, and **Regression to the Mean**

This chapter covers Trend Curve, Correlation, and Regression to the Mean and consists of 12 topics.

**In this video we show how to find the line best fitting a set of bivariate data.**

**In this video we show how the RSQ, SLOPE, INTERCEPT, and STEYX functions can be used to analyze a linear regression.**

**In this video we show how exponential growth can be used to fit bivariate data where the graph gets steeper.**

**In this video we show how to fit a constant elasticity or power demand curve.**

**In this video we show how to fit a quadratic demand curve based on 3 data points.**

**In this video we show how to estimate a Learning curve which shows how the unit cost of producing a product drops as more of the product has been produced.**

**In this video we show how the concept of correlation can measure the strength of the linear association between monthly returns on six stocks.**

**In this video we show how the SLOPE function can be used to quickly estimate the Betas of many stocks.**

**In this video we show how the idea of Regression to the Mean explains why NFL teams that do well in one season usually do not do as well the next season.**

**In this video we show how to summarize quarterly Amazon.com revenues with a Moving Average Chart and smooth out the trend and eliminate seasonality.**

**In this video we show that an R Squared value of 0.99 is not necessarily that good.**

**In this video we show how to determine which of the linear, exponential, and power curve best fit a bi-variate relationship.**

## Multiple **Regression**

This chapter covers Multiple Regression and consists of 19 topics.

**In this video we show how multiple regression is used to predict computer sales per-capita based on education and income.**

**In this video we continue from the previous example.**

**In this video we show how to incorporate qualitative variables such as seasonality to predict auto sales.**

**In this video we show how conjoint analysis can determine what product attributes are valued by customers.**

**In this video we show how an actual regression is used to illustrate conjoint analysis.**

**In this video we show how conjoint analysis is used to estimate the value of product attributes.**

**In this video we show how to include nonlinearities and interactions in a multiple regression analysis.**

**In this video we use multiple linear regression to show what makes NBA teams win.**

**In this video we show how to use multiple regression to closely approximate the NFL’s QB Rating formula.**

**In this video we show how Maximum Likelihood Estimation is used to estimate statistical parameters.**

**In this video we show logistic regression can be used to predict dependent variables that can assume two outcomes (such as Live or Die after an operation.)**

**In this video we explain how to do logistic regression with grouped data**

**In this video we use multiple regression with nonlinearities and interactions to reverse engineer the complex Windchill Index formula.**

**In this video we show how to use multiple regression to predict prices of Bordeaux wine based on temperature and rainfall.**

**In this video we show how to perform a One-Way ANOVA.**

**In this video we show how to perform a Two-Way ANOVA with no replication.**

**In this video we show how to analyze a Two-Way ANOVA with no interaction.**

**In this video we show how to analyze a Two-Way ANOVA with interaction.**

**In this video we show how to determine how 3 or more factors can be set to optimize a dependent variable.**

**Probability**

This chapter covers Probability and consists of 10 topics.

**In this video we define the concept of an experiment, sample space, and solve some simple probability problems.**

**In this video we define mutually exclusive events and show how to compute P(A or B) for events A and B.**

**In this video we explain how to use the Rule of Complements to solve tricky probability problems.**

**In this video we define independent events and show how easy it is to compute P(A and B) for independent events.**

**In this video we show how to compute the probability of one event given that another event has occurred.**

**Leading up to Bayes theorem, In this video we develop the Law of Total Probability.**

**In this video we show how Bayes Theorem allows us to use information to update probabilities.**

**In this video we show how Bayes Rule can be used to efficiently classify observations into one of several groups**

**In this video we calculate the prior probabilities and likelihoods needed to implement Naïve Bayes**

**In this video we show how to use Naïve Bayes to compute for any observation posterior probabilities for each group**

## Random **Variables**

This chapter covers Random Variables and consists of 12 topics.

**In this video we show how to compute the mean, standard deviation, and variance of random variables.**

**In this video we show how to compute probabilities involving repeated trials with two outcomes (success or failure).**

**In this video we discuss the mean, variance and standard deviation of a binomial random variable.**

**In this video we discuss how the Hypergeometric random variable can be used to calculate probabilities involving sampling without replacement.**

**In this video we show how to calculate probabilities involving the Negative Binomial Random Variable.**

**In this video we show how to compute Poisson probabilities in situations where in a small length of time either 0 or 1 events occur.**

**In this video we show how to calculate probabilities involving the Exponential Random Variable.**

**In this video we show how to compute probabilities for the normal random variable.**

**In this video we show how to use the normal random variable to approximate probabilities involving sums of >=30 random variables.**

**In this video we show how Z scores are used to measure how “unusual” a data point is.**

**In this video we use the Weibull Random Variable to model machine lifetimes.**

**In this video we use the Beta random variable to model activity durations.**

## Array **Formulas**

This chapter covers Array Formulas and consists of 17 topics.

**In this video we show how array formulas and functions can fill more than one cell.**

**In this video we use simple examples to enhance your understanding of array formulas.**

**In this video we show how to use array formulas to find the total length of cells in a range or the length of the longest cell in the range.**

**In this video we show how to find the 6 smallest numbers in a range.**

**In this video we show how array formulas can emulate the SUMIF and DSUM functions.**

**In this video we show how an array formula can emulate the SUMIFS functions.**

**In this video we show how an array formula can select rows for a conditional sum based on an “OR” criteria.**

**In this video we use an array formula to create a MEDIANIF function.**

**In this video we use an array formula to create a Standard Deviation If function.**

**In this video we show how the SUMPRODUCT function can be used to set up a criteria used to select rows in which columns are multiplied.**

**In this video we use an array formula to find all unique entries in an array.**

**In this video we show how the TRANSPOSE function can be used to dynamically transpose a row of data into a column.**

**In this video we show how the FREQUENCY array function can be used to count the number of observations in a data set that fall into several bin ranges.**

**In this video we show how to Multiply and Invert matrices.**

**In this video we show how to use multiple regression and the TREND array function to predict US Auto Sales.**

**In this video we show how the LINEST array function can be used to run a multiple regression which automatically updates when new data is added.**

**In this video we use an array formula to determine if an integer is prime and then discuss Fermat’s Little Theorem, a beautiful result from number theory**

## Offset and **Indirect Functions**

This chapter covers the Offset and Indirect Functions and consists of 18 topics.

**In this video we define the complex, powerful, and often misunderstood OFFSET function.**

**In this video we use the OFFSET function to emulate a VLOOKUP when the look value is matched in the right-hand and not left-hand column of a spreadsheet range.**

**In this video we show how the OFFSET function can be used to create (in 2 minutes) thousands of formulas that create Phase I, II, and III costs for a drug.**

**In this video we show how the OFFSET, MATCH, and INDEX functions can analyze tax data imported from a PDF.**

**In this video we show how the OFFSET function is used to return the most recent month of sales.**

**In this video we show how to create a dynamic range that automatically expands when new rows or columns of data are added.**

**In this video we create a chart that always shows the last 6 months of sales.**

**In this video we define the complex, powerful, and often misunderstood INDIRECT function.**

**In this video we use the INDIRECT function to consolidate data from any subset of cells in any subset of worksheets into a single summary.**

**In this video we use the INDIRECT function to efficiently copy formulas involving Range Names.**

**In this video we show how to easily find all the worksheet names in a workbook.**

**In this video we show how to create a Nested Dropdown box where the entries in the second dropdown box depend on the value selected in the first dropdown box.**

**In this video we show how to use the INDIRECT function to move monthly stock return data in 30 different worksheets to a single worksheet where the data can be analyzed.**

**In this video we begin to show how to use a country’s GINI coefficent (a measure of income inequality) to determine the entire distribution of a country’s income**

**In this video we finish showing how to use a country’s GINI coefficent (a measure of income inequality) to determine the entire distribution of a country’s income**

**In this video we explain the concept of “wasted votes” and show how this metric may determine the future of American politics**

**In this video we show how to find the average points scored by an NBA team during their last 5 games.**

**In this video we use the OFFSET function and Data Tables to show that a Moving Average Trading Rule does not beat Buy and Hold.**

## Optimization and the **Excel Solver**

This chapter covers Optimization and the Excel Solver and consists of 27 topics.

**In this video we introduce the concept of optimization and show how the Excel Solver can be used to find a profit-maximizing product mix.**

**This video builds upon the previous video on Solver Optimization.**

**This video builds upon the previous two videos on Solver Optimization.**

**In this video we show how to find the cost minimizing distribution strategy that ships products from plants to customers.**

**In this video we show how to use the Excel Solver to find cost minimizing workforce schedules.**

**In this video we show how to choose the subset of 20 projects that maximizes NPV subject to limited resources.**

**In this video we show how binary changing cells can be used to select Daily Fantasy Lineups.**

**This video is a continuation of the previous Fantasy Sports Example.**

**In this video we show how to use the Solver to solve for monthly mortgage payments and your needed annual retirement contribution.**

**In this video we show how to predict winners of NFL games based on past scores.**

**In this video we show how to use scores of past NFL games to predict scores of future games.**

**In this video we show how to estimate the demand for lipstick and use Solver to find a profit-maximizing price.**

**In this video we show how to locate one or two warehouses to minimize the distance shipments travel to customers.**

**In this video we give a complex formula that approximates (based on latitude and longitude of two locations anywhere on Earth) the distance between the two locations.**

**In this video we show how to determine for a given percentage price change, the change in demand needed to break even.**

**In this video we show how to determine how much money needs to be saved each year so that in 10 years we have accumulated one year of salary.**

**In this video we show how to use the Solver to determine a sampling plan that has a high chance of accepting a batch with a low fraction of defectives and a high probability of rejecting a batch with a high fraction of defectives.**

**In this video we show how to use the Solver to assign school bus drivers to bus routes.**

**In this video we show how to minimize the cost of shipping a product from plants through warehouses to customers.**

**In this video we show how to use solver to solve a simple inventory problem.**

**In this video we show how to use the Solver to find the shortest route between two nodes.**

**In this video we show how Solver can be used to develop a hypothesis test to determine if students at a school have an average IQ larger than 100.**

**In this video we use the Solver to allocate an investment portfolio to gold, REITs, stocks, t-bills, and bonds.**

**In this video we show how to use the Solver to minimize the cost of sending material from one place to another.**

**In this video we use the solver to find the cheapest diet of McDonald’s foods that meet various nutritional constraints**

**In this video we use Solver to determine the exam score needed on a final to obtain a desired course grade when the low test counts for 1/2 the weight of other tests**

**In this video we show how Solver can be used to improve the results of a negotiation for both parties**

**Forecasting**

This chapter covers Forecasting and consists of 12 topics.

**In this video we show how to estimate the trend and seasonal indices for monthly US airline miles.**

**In this video we show how the Ratio to Moving Average Method is used to forecast time series in the presence of Trend and Seasonality.**

**In this video we conclude our discussion of the Ratio to Moving Average Method.**

**In this video we show how Winter’s exponential smoothing method can be used to forecast in the presence of Trend and seasonality.**

**This video is a continuation of the Winter’s Method of forecasting.**

**This video is a continuation of the Winter’s Method of forecasting.**

**This video is the implementation and conclusion of the Winter’s Method of forecasting.**

**In this video we show how to incorporate seasonality and special factors in a forecast of a bank’s daily customer count.**

**In this video we finish our discussion of forecasting daily customer count at a bank.**

**In this video we show how to use the Excel Solver to determine how seasonality, PC sales, and launch dates affect software sales.**

**In this video e show how the Brier score can be used to evaluate forecasters that predict binary outcomes (rain or no rain, home team wins NFL game, etc.)**

**In this video we show how to use the Solver to determine how our price, the competitor’s price, and whether the product is on display influences candy bar sales.**

**Charting**

This chapter covers Charting and consists of 28 topics.

**In this video we create Combination charts and Charts with a Secondary Axis.**

**In this video we show options for handling missing data in charts.**

**In this video we show options for handling hidden data in charts.**

**In this video we show how to add pictures to your column charts.**

**In this video we show how to add Data Labels to a column chart.**

**In this video we show how to add a Data Table below a column chart.**

**In this video we show how to label points with labels based on cells in Excel. This is for users of Excel 2013 and Excel 2016.**

**In this video we show how to use the WINGDINGS3 font to create customized Icon sets.**

**In this video we show how to create a Band Chart to summarize how inventory moves between lower and upper control limits. We also show how to create and use Chart Templates.**

**In this video we show how to create Thermometer charts that summarize monthly performance against a target.**

**In this video we show how to create Chart Titles and Series Labels that dynamically update as we change our spreadsheet inputs.**

**In this video we show how to use Check**

**B**

**oxes to control which series show in a chart.**

**In this video we show how to use List Boxes to control which series shows in a chart.**

**In this video we show how to create a Spinner that enables you to change the values of any number of spreadsheet inputs by clicking on your Spinners.**

**In this video we show how to create a Gantt Chart that monitors completion times of project activities.**

**In this video we show how to create a dynamic histogram that updates automatically when new data is added.**

**In this video we show how use a column chart to summarize sales in 50 states where the columns are listed in descending order of sales.**

**In this video we show how to color monthly sales green when we have a good month, yellow for a typical month, and red for a bad month.**

**In this video we show how to create Waterfall Charts with Excel 2013.**

**In this video we use GETPIVOTDATA and the Excel Table feature to create dynamic charts based on a PivotTable.**

**In this video we show how to create Pareto Charts in Excel 2013.**

**In this video we show how to place a vertical line in a chart that moves when the chart is relocated.**

**In this video we use Radar charts to summarize employee attributes on multiple dimensions.**

**In this video we use a Bubble Chart to summarize sales in different countries on three dimensions.**

**In this video we use Sparklines to visually summarize daily customer counts in different bank branches in a single cell for each branch.**

**In this video we use Win Loss Sparklines to visually summarize week by week NFL results.**

**In this video we show how to replace the bars in a column graph by a picture (in this case a soda bottle).**

**In this video we show how to stack pictures in a column graph so that, for example 1 plane = sales of 50 units.**

## Keystroke **Shortcuts**

This chapter covers Keystroke Shortcuts and consists of 8 topics.

**In this video we show Keystroke shortcuts that are useful in formatting cells.**

**In this video we show keystroke shortcuts that speed up worksheet movement.**

**In this video we show Keystroke shortcuts that help you better understand your spreadsheet’s formulas.**

**In this video we show keystroke shortcuts that speed up selection of cells or worksheets.**

**In this video we show how to create Hyperlinks that enable you to click on the Hyperlink and move anywhere in your Workbook.**

**In this video we show how to link data and/or charts in Excel to Word so that changes in the Excel Source data are reflected in the Word document.**

**In this video we show how to protect the formulas in your worksheet so that the user can change the spreadsheet inputs but not alter your formulas.**

**In this video we show how the Format Painter or F4 key can be used to quickly copy formats.**

## Power **View**

This chapter covers Power View and consists of 5 topics.

**In this video we introduce the cool charting capabilities of Power View by summarizing iced tea sales.**

**In this video we show how to use Tiles to chart tea sales in a selected state.**

**In this video we show how to use a map to summarize iced tea sales in each state.**

**In this video we show how Power View Multiples quickly create a separate chart summarizing sales of each product sold.**

**In this video we show how to create a “movie” that shows how three US crime statistics change over time.**

## Monte Carlo **Simulation**

This chapter covers Monte Carlo Simulation and consists of 14 topics.

**In this video we introduce Monte Carlo Simulation. Monte Carlo can model the probability distribution of outcomes in the presence of uncertainty.**

**In this video we illustrate the RAND and RANDBETWEEN functions which are the key to Excel simulations.**

**In this video we show how to simulate a discrete random variable.**

**In this video we show how to simulate a normal random variable.**

**In this video we simulate the number of runs a baseball team scores in an inning.**

**In this video we use simulation to estimate the probability that if n people are in the room at least two people have the same birthday.**

**In this video we show how to determine the probability of winning at craps.**

**In this video we show how to determine an order or production quantity that maximizes expected profit in the presence of uncertain demand.**

**In this video we show how to determine a bid that maximizes expected profit given the uncertainty about competitor’s bidding behavior.**

**In this video we show how to “draw” a five card poker hand and calculate the chance of getting two pair.**

**In this video we show how simulation can be used to estimate the chance of each team winning the NBA finals.**

**In this video we answer the question, “If I have $2 and play an unfair game, what is the chance I get to $6 before going bankrupt?”**

**In this video we answer the question, “If a stock is equally likely to return 60% or -40% each year, is it a good stock?” You might be surprised!**

**In this video we show how to determine the average length of the longest winning streak for a team that wins 70% of their games.**

## Statistical Quality **Control**

This chapter covers Statistical Quality Control and consists of 10 topics.

**In this video we show how to determine the process capability of a process.**

**In this video we show how Motorola determined that a process should have specifications be six sigma from the process mean.**

**In this video we show how P-Charts are used to determine if the fraction defective is in control.**

**In this video we show how to use an x-bar chart to determine if the level of a process is in control.**

**In this video we show how to use an R-Chart to determine if a process’ variability is in control.**

**In this video we use a moving range chart to determine (based on individual measurements) if a process is in control.**

**In this video Deming’s funnel experiment is used to show why it is so important to determine if a process is in control.**

**In this video we use the Evolutionary Solver to determine optimal sampling plans that can be used to determine whether to accept or reject a product batch.**

**In this video we show how to determine an optimal sampling plan when the population size is small.**

**In this video we show how to use the Taguchi Loss Function to compare production processes.**

## Advanced **Solver**

This chapter covers Advanced Solver and consists of 21 topics.

**In this video we show how the Simplex LP solver finds an optimal solution.**

**In this video we show how the GRG solver finds an optimal solution to “smooth” nonlinear optimization problems.**

**In this video we show how the EVOLUTIONARY solver finds an optimal solution to “non-smooth” nonlinear optimization problems.**

**In this video we show how the GRG Solver optimally locates a single warehouse.**

**In this video we show how the GRG Multistart Solver optimally locates two warehouses.**

**In this video we show how the Evolutionary Solver uses penalties to solve optimization problems using non-smooth functions such as IF and COUNTIF.**

**This video is the conclusion of the previous example of Assigning Workers to Jobs.**

**In this video we show how John Deere used the Evolutionary Solver and the concept of target cell “penalties” to schedule the production of riding mowers.**

**In this video we show how the Evolutionary Solver is used to solve sequencing problems.**

**In this video we use the GRG Multistart solver to estimate product and cross product elasticities.**

**In this video we show how to fit customized learning curve models.**

**In this video we show how to use the Evolutionary Solver to solve the classic Bin Packing Problem.**

**In this video we use the Evolutionary Solver to determine a final exam schedule that eliminates student conflicts.**

**In this video we show how to use the Evolutionary Solver to develop a linear scoring rule that effectively classified data points into one of two groups.**

**In this video we use the Evolutionary Solver to show that every US city is demographically similar to either LA, Memphis, SF, or Omaha.**

**In this video we use the evolutionary solver to find the minimum cost method of providing sufficient process capacity to meet demand.**

**In this video we use the DIF capabilities of the Evolutionary Solver to implement the Toyota goal chasing method for scheduling car production.**

**In this video we show how to use the Evolutionary Solver to schedule high school workers at a fast food restaurant**

**In this video we use the Evolutionary Solver to solve two difficult logic puzzles.**

**In this video we show how to locate hospital departments to minimize distance traveled.**

**In this video we use the Evolutionary Solver to solve two more difficult logic puzzles.**

## Negotiation

This chapter covers the new Negotiation and consists of 9 topics.

**In this video we show how to fairly divide property in a divorce settlement.**

**In this video we conclude our discussion of fairly dividing property in a divorce settlement**

**In this video we show why it is reasonable to maximize POP (Proportion of Potential).**

**In this video we show how a store and a construction contractor can negotiate on multiple issues.**

**In this video we continue our discussion of how a store and a construction contractor can negotiate on multiple issues.**

**In this video we conclude our discussion of how a store and a construction contractor can negotiate on multiple issues.**

**In this video we show how to allocate property among 3 participants.**

**In this video we show how 6 segments of Congress can compromise on difficult issues.**

**In this video we show how to determine a person’s level of satisfaction if negotiations fall through.**

## Excel 2016 **Charts**

This chapter covers Excel 2016 Charts and consists of 11 topics.

**In this video we show how to use Excel 2016 to create much improved histograms.**

**In this video we show how to use Excel 2016 to create much improved Pareto charts.**

**In this video we show how to use Excel 2016 to create a Boxplot to summarize the results of the 1970 Draft Lottery.**

**In this video we show how to use Excel 2016 to create a Boxplot that summarizes multiple test scores at three high schools.**

**In this video we show how to use an Excel 2016 Waterfall chart to summarize the progress of sales during a year.**

**In this video we show how to use an Excel 2016 Waterfall chart to summarize how various cost centers reduce Company Revenue and Profit.**

**In this video we use an Excel 2016 TreeMap or Mosaic chart to summarize hierarchical bookstore sales data.**

**In this video we use an Excel 2016 Sunburst chart to summarize hierarchical bookstore sales data.**

**In this video we use an Excel 2016 Sunburst chart to summarize quarterly and monthly sales data.**

**In this video we use the SUMIFS functions to prepare restaurant sales data and then summarize the sales data with a TreeMap chart.**

**In this video we show how to create a funnel chart which shows the winnowing of a pool of doners from prospects to bid doners**

## Interview **Questions**

This chapter covers Interview Questions and consists of 15 topics.

**In this video we determine the 10 hour time period in a a two week period that has the most Uber pickups.**

**In this video we breakdown the percentage of Uber calls by hour of the day.**

**In this video we breakdown for each day, the fraction of calls that occur during each hour.**

**In this video we determine the number of Uber drivers working each shift.**

**In this video we show how to change the old name for an airline to its current name.**

**In this video we solve the famous Wall Street Interview “Opening Locker” problem.**

**In this video we use the Evolutionary Solver capability to create a 3×3 Magic Square.**

**In this video we show how to use the Excel Solver and MOD function to solve a classic Wall Street Interview Question.**

**In this video we use IF, IFERROR, MATCH, INDEX, and Text functions to find a company’s abbreviation from a list of abbreviations and then highlight the abbreviation within a company’s name.**

**In this video we show how to place an Olympic athlete’s information in a single row**

**In this video we find the best time for each country in Women’s 200M and put all the information for that performance in a single row**

**In this video sparklines are used to summarize trends in various attributes of the Summer Olympics**

**In this video we show how to use Excel functions to answer interesting questions about the Men’s 2016 Olympic 200m race**

**In this video we use the INDEX/MATCH combination to answer many questions about Olympic Gymnastics**

**In this video we use PivotTables to analyze the Olympic 4x400M relay**

**Macros**

This chapter covers Macros and consists of 10 topics.

**In this video we show how to make the Developer Tab appear on your ribbon.**

**In this video we record a Macro that inserts a list of team members starting in cell A1. We also create a button to run the macro.**

**In this video we show how to use Relative References to make sure our list of names can be entered anywhere in our workbook.**

**In this video we create a macro to apply a desired format to a range of selected cells and show how to run the Macro by clicking on a picture (in this case an Aircraft Carrier).**

**In this video we show how to create a macro that formats a range of variable size.**

**In this video we create a macro that coverts formulas in a selected range to numerical values.**

**In this video we create macros that enable us to toggle in a PivotTable between a view that shows sales for all customers and sales for only our top 20 customers.**

**In this video we show how to create a macro that places a border around a range of selected cells.**

**In this video we discuss how to save a macro to your Personal Macro Workbook.**

**In this video we show how to create a macro that will sort a list by last and first name.**

## Supply **Chain**

This chapter covers Supply Chain and consists of 47 topics.

**In this video we use optimization to find a minimum cost production schedule that meets demand on time.**

**In this video we show how to create a linear solver model which allows for shortage costs as well as holding costs.**

**In this video we extend our Solver Inventory model to account for hiring and firing workers.**

**In this video we discuss the famous EOQ formula, which recommends the cost minimizing order trading off holdings and ordering costs.**

**In this video we show how Power of Two ordering policies simplify the coordination of ordering when several products are involved.**

**In this video we show how coordinating ordering of multiple products can reduce supply chain costs.**

**In this video we show how to determine the optimal order quantity when large order quantities result in quantity discounts.**

**In this video we define Cycle Service Level (CSL) and show how to determine a reorder point and safety stock that results in a desired CSL.**

**In this video we define Fillrate and show how to determine a reorder point and safety stock that result in a desired Fillrate.**

**In this video we show how to find the cost minimizing distribution strategy that ships products from plants to customers.**

**In this video we show how to use the Solver to assign school bus drivers to bus routes.**

**In this video we show how to use the Solver to find the shortest route between two nodes.**

**In this video we show how to minimize the cost of shipping a product from plants through warehouses to customers.**

**In this video we show how to use the Solver to minimize the cost of sending material from one place to another.**

**In this video we show how the GRG Solver optimally locates a single warehouse.**

**In this video we show how the GRG Multistart Solver optimally locates two warehouses.**

**In this video we use a two-way data table to find the distances between each pair of 100 world cities.**

**In this video we locate 4 warehouses to optimally serve 100 cities.**

**In this video we show how the Evolutionary Solver uses penalties to solve optimization problems using non-smooth functions such as IF and COUNTIF.**

**This video is the conclusion of the previous example of Assigning Workers to Jobs.**

**In this video we show how John Deere used the Evolutionary Solver and the concept of target cell “penalties” to schedule the production of riding mowers.**

**In this video we show how the Evolutionary Solver is used to solve sequencing problems.**

**In this video we show how to extend the TSP to a situation where a truck must return to its starting point before completing its route.**

**In this video we show how to schedule jobs to minimize number of late jobs and total days jobs are late.**

**In this video we use the DIF capabilities of the Evolutionary Solver to implement the Toyota goal chasing method for scheduling car production.**

**In this video we discuss how to compute the average number of people in line and the average time to spend waiting in line.**

**In this video we show how to use Little’s Law to compute a firm’s inventory turns.**

**In this video we use the evolutionary solver to find the minimum cost method of providing sufficient process capacity to meet demand.**

**In this video we introduce Monte Carlo Simulation. Monte Carlo can model the probability distribution of outcomes in the presence of uncertainty.**

**In this video we illustrate the RAND and RANDBETWEEN functions which are the key to Excel simulations.**

**In this video we show how to simulate a discrete random variable.**

**In this video we show how to simulate a normal random variable.**

**In this video we show how to determine an order or production quantity that maximizes expected profit in the presence of uncertain demand.**

**In this video we show how to locate hospital departments to minimize distance traveled.**

**In this video we show how to locate three warehouses and ship materials to minimize the total distance the shipments travel.**

**In this video we show how to locate three warehouses and ship materials to maximize the number of shipments within 250 miles of a warehouse.**

**In this video we determine the minimum number of warehouses needed to ensure that each shipment travels <=250 miles.**

**In this video we show how to minimize the distance shipments travel when three warehouses each have limited capacity.**

**In this video we show how to determine weights for attributes that influence supplier selection.**

**In this video we use a linear Solver model to determine how to add capacity to meet future requirements.**

**In this video we explain why the cost of adding capacity is often not a linear function of the amount of capacity added.**

**In this video we show how to use the Evolutionary Solver to solve Line balancing problems.**

**In this video we show the importance of flexible capacity.**

**In this video we show how to use simulation to estimate the length of time needed to complete a project.**

**In this video we use simulation to show how variability reduces throughput and a bottleneck controls throughput.**

**In this video we use simulation to show how variability of order quantities increases as you move upstream in a supply chain.**

**In this video we show how to estimate actual demand when a company runs out of a product.**

## Office **365 Functions**

This chapter covers the new Office 365 Functions and consists of 4 topics.

**In this video we show how the TEXTJOIN function can be used to make concatenation easier.**

**In this video we show how to use the MAXIFS and MINFS Office 365 functions.**

**In this video we show how the IFS Office 365 function simplifies the creation of nested IF formulas.**

**In this video we show how the SWITCH function can be used as an alternative to the IFS or CHOOSE function.**