info@excelwithwayne.com

Report

Harassment or bullying behavior
Contains mature or sensitive content
Contains misleading or false information
Contains abusive or derogatory content
Contains spam, fake content or potential malware

Block Member?

Please confirm you want to block this member.

You will no longer be able to:

  • See blocked member's posts
  • Mention this member in posts
  • Invite this member to groups
  • Message this member
  • Add this member as a connection

Please note: This action will also remove this member from your connections and send a report to the site admin. Please allow a few minutes for this process to complete.

Report

You have already reported this

BASIC FORMULAS INDEX, MATCH, AND LOOKUPS

This chapter covers Basic Formulas and consists of 43 lessons.
 
  • 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.
  • Bringing PDF Tables Into Excel – In this video we show how to import tables from a PDF file into Excel.
  • Multiplying 4 Consecutive Integers – In this video we use Excel formulas to demonstrate that whenever you multiply 4 consecutive integers and add 1 you get the square of an odd integer.
  • Explaining Google’s Page Rank – In this video we discuss the intuitive basis for Google’s Page Rank algorithm, which is used to rank web pages that are shown in response to search requests.
  • The XLOOKUP Function – In this video we introduce the incredibly powerful XLOOKUP function, which makes HLOOKUP and VLOOKUP functions obsolete!
  • XLOOKUP and the TRUE Argument – In this video we show how the XLOOKUP function can return multiple arguments when data for each item is in different rows (not columns) and how XLOOKUP can extend the VLOOKUP function with the TRUE argument.
  • XLOOKUP and the INDEX MATCH Combination – In this video we show how to use the XLOOKUP function to extend the INDEX MATCH combination.
  • Using XLOOKUP to reference a cell Range – In this video we show how XLOOKUP can be used to reference a range of cells in a table based on the start  and end row (or column) chosen.

FILTERING, SORTING, AND REMOVING DUPLICATES

This chapter covers Filtering, Sorting, and Removing Duplicates and consists of 16 lessons.

  • Basic Filters – This video shows how to filter based on selecting entries in a column.
  • Number Filters – This video shows how to filter based on numerical data.
  • Top 10 Filtering – This video shows how to pick out top 10, bottom 10, and the top 5% from a column.
  • Date Filters – This video shows how to filter based on dates.
  • Text Filters – This video shows how to filter based on text.
  • Color Filters – This video shows how to filter based on cell or font color.
  • Filtering a Column on Two Criteria – This video shows how to filter a single column based on text and cell color.
  • Basic Sorting – This video shows how to sort based on text, numbers, and dates.
  • Sorting on Colors – This video shows how to sort based on cell or font color.
  • Sorting A to Z – This video shows how to sort without using the sort dialog box.
  • Case Sensitive Sorting – This video shows how to sort with lower case entries coming before upper case entries.
  • Sorting With a Custom List – This video shows how to sort using a custom list.
  • Creating a Custom List – This video shows how to create a custom list and use it in sorting.
  • Sorting Across – This video shows how to sort across a row instead of a column.
  • Removing Duplicates – This video shows how to remove duplicates.
  • Filter by Selection – This video shows how you can filter a spreadsheet database simply by selecting items in the database. For example, selecting a player on the Boston Red Sox will filter the database to  show only members of the Red Sox.

RANGE NAMES

This chapter covers Range Names and consists of 9 lessons.

  • Name Box – This video shows how to create range names using the Name Box.
  • Create From Selection – 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.
  • F3 Trick – This video shows how to use the F3 key to paste a range name into a formula.
  • Name Entire Row or Column – This video shows how to name an entire row or column.
  • Apply Names – This video shows how to ensure that if you create range names, they will show up in previously created formulas.
  • Worksheet and Workbook Names – This video shows how you can create range names that apply to only a single worksheet or the entire workbook.
  • Range Name Tricks – This video shows how to use a named row in a formula and create ranges based on Excel formulas.
  • Implicit Intersection – This video shows how to pick off an entry in an array by simply typing the row and column headings.
  • Vertical Lookup With Range Names – This video shows how to use Range names in VLOOKUP formulas.

TEXT FUNCTIONS

This chapter covers Text Functions and consists of 15 lessons.

  • Left, Right, Mid, Len, and Concatenate Functions – This video shows how to use the LEFT, RIGHT, MID, LEN and CONCATENATE functions to manipulate text in a single cell.
  • Find Function and Text to Columns – This video shows how to use the FIND function and TEXT to COLUMNS to parse data in a single cell.
  • Histograms – This video shows how to use the REPT function to create a simple histogram.
  • Clean Function – This video shows how to Use the CLEAN, FIND, and SUBSTITUTE functions to remove invisible characters from your data.
  • Flash Fill – This video shows how to use FLASH FILL in Excel 2013 and 2016 to automatically perform the work of text functions.
  • Control + E and Flash Fill – In this video we show how Control + E makes Flash Fill perform better.
  • Finding the Last Word – This video shows how to use the SUBSTITUTE function to find the last word in a cell.
  • Two Column Lookup – This video shows how to use a VLOOKUP based on multiple columns of data.
  • Social Security Numbers – This video shows how to turn Social Security Numbers into PeopleSoft IDs.
  • Unicode – This video shows how to use Excel’s vast library of UNICODE characters.
  • Creating an Email List With Text Functions – This video shows how to take a list of email aliases and turn it into an email list.
  • The TEXT Function – 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.
  • The ROUND, ROUNDUP, ROUNDDOWN, and INT Functions – In this video we show how to use the ROUND, ROUNDUP, ROUNDDOWN, and INT functions to round numbers in any desired fashion.
  • Special Text Formats – In this video we show how to format zip codes, social security numbers, phone numbers, beginning zeroes, and trailing zeroes.
  • Using Flash Fill to Clean Messy Data – This video shows how to use Flash Fill to eliminate invisible characters. When using a VLOOKUP, invisible characters will return an error message.

DATE, TIME, AND FINANCIAL FUNCTIONS

This chapter covers Date, Time, and Financial Functions and consists of 27 lessons.

  • Date Functions Part One – This video shows how to use the YEAR, DAY, WEEKDAY, DATE, WORKDAY, and NETWORKDAYS functions to manipulate dates.
  • Date Functions Part Two – This video shows how to use the DATEDIF function to find years or months between two dates.
  • Static Time and Date – This video shows how to insert a Static Date (a date that does not change) into a spreadsheet.
  • The EOMONTH and EDATE Functions – This video shows how to use the EOMONTH function to find the last day of a given month and how to use the EDATE function to move ahead or behind several months from the given date.
  • The WEEKNUM Function – This video shows that for a given date the WEEKNUM function can be used to determine the week of the year.
  • Introduction to Time Functions – In this video we discuss how Excel handles times and explain the TIME, HOUR, MINUTE, and SECOND functions.
  • The TIMEVALUE function and Time Intervals – In this video we discuss the TIMEVALUE function and how to handle differences in times.
  • Creating Time Sequences – In this video we show how to create a sequence of regularly spaced times, how to add up times, and add a constant number to a list of times.
  • Custom List – This video shows how to create a Custom List (like all NFL teams) that you can drag to Autofill.
  • MLK Day – This video shows how to find the date of Martin Luther King Day for a given year.
  • Net Present Value – This video shows how to use the NPV function to find the present value of a sequence of cash flows.
  • XNPV – This video shows how to use the XNPV function to find the present value of irregularly spaced cash flows.
  • Internal Rate of Return – This video shows how to use the IRR function to find the Internal Rate of Return of a sequence of cash flows.
  • Modified Internal Rate of Return and XIRR – 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.
  • Modified Internal Rate of Return – In this video we discuss the MIRR Function which improves on the IRR function.
  • Present Value and Future Value Functions – 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.
  • The FVSHEDULE Function – This video shows how the FVSCHEDULE function is used to find the future value when interest rates vary over time
  • Mortgage Functions – This video shows how to use the PMT, IPMT, PPMT, CUMPRINC, and CUMIPMT functions to analyze loans and mortgages.
  • Rate and NPER Functions – 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.
  • Percentage of Principle – In this video we calculate the fraction of our loan size that we pay to the bank each year.
  • Bond Pricing – In this video we use Excel financial functions to determine a fair price for a bond.
  • Annual Percentage Rate on Mortgage Loans – In this video we use Excel’s financial functions to explain the relationship between loan expenses and a loan’s APR
  • Excel’s Depreciation Functions – In this video we demonstrate Excel functions that make it easy to compute period by period depreciation.
  • Compound Interest – In this video we show how Excel’s financial functions can be used to solve problems involving compound interest.
  • Growing Annuities – In this video we show how to use Excel’s financial functions to value a growing annuity.
  • Adjustable Rate Mortgages (ARM’s) – In this video we show how Excel’s financial functions can be used to compute payments on an Adjustable Rate Mortgage.
  • Scheduling a Project – In this video we show how to determine the starting dates for each activity in a project that ensure the project is completed on time.

AUDITING TOOLS, CIRCULAR REFERENCES, AND IF FUNCTIONS

This chapter covers Auditing Tools, Circular References, and IF Functions and consists of 24 lessons.

  • Auditing – 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.
  • Circular Reference – This video shows how circular references can be used and resolved.
  • IF Statements – This video shows how to use IF statements to calculate purchasing costs and analyze the game of craps.
  • Odds and Evens – This video explains how to use IF statements and the MOD function to determine the winner in odds and evens.
  • Annual Revenue – This video shows how to use IF statements to extract annual sales from quarterly sales data.
  • Product Life Cycle – This video shows how to use IF statements to model sales during the product life cycle.
  • Flatten Table – This video shows how to use IF statements to extract the data in a 20 row by 12 column table into 240 different rows.
  • Error Trap – 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.
  • NFL Scores – 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.
  • The ISERROR Function – Inthis 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.
  • Divide by 7 – This video shows how to determine if a check number like 2453589-6 has the sum of digits divisible by 7.
  • Valuation – This video shows how to use a two stage growth model to reverse engineer a stock price.
  • Combining Addresses – 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.
  • IF Statements And Sales Commission Calculations – This video shows how to use IF statements to compute sales commission based on a tiered commission structure.
  • IF Statements And Sales Commission Calculations Part Two – In this video we use VLOOKUP, MATCH, INDEX, And IF functions to model a complex sales incentive plan.
  • IF Statements And Option Investing – This video shows how IF statements can be used to model investment strategies involving options.
  • IF Statements Continued – In this video we use IF statements to model customer value, insurance premiums, and a person’s future salary.
  • Basic Shift Code – 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.
  • Rail Fence Code – In this video we show how to use IF Statements and Text Functions to Implement the Famous Rail Fence Code.
  • Longest Winning Streak – In this video we use IF statements to find the longest winning streak by the Washington Nationals during the 2016 season.
  • The Ducci Sequence – In this video we show how to use IF statements and the MATCH function to study the Ducci sequence
  • The Stern Sequence – In this video we use IF statements, the ISEVEN function, and the INDEX function to model the amazing Stern Sequence
  • How does waist to height ratio affect your longevity? – 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
  • Combining the IF and IFERROR Functions – In this video we show how to combine the IF and IFERROR functions in a single formula that is used to determine if a given school is an elementary school.

INQUIRE ADD-IN

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

  • Introduction to the Inquire Add-In – In this video we show how to install the Inquire Add-In and describe its capabilities.
  • Comparing Workbooks with the Inquire Add-In – In this video we show how to compare workbooks using the Inquire Add-In.
  • Analyzing a Workbook – In this video we show how to analyze the key elements of a workbook.
  • Analyzing Links Between Workbooks – In this video we show how to create a diagram that describes links between workbooks.
  • Analyzing Links Between Worksheets – In this video we show how to create a diagram that describes links between workbooks and worksheets.
  • Analyzing Precedents and Dependents of a Given Cell – In this video we show how to create cool diagrams that extend Trace Precedents and Trace Dependents.
  • Cleaning Excess Formats – 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 18 lessons.

  • One Way Data Table – This video shows how to use a One Way Data Table to determine how changing a single spreadsheet input affects multiple outputs.
  • Two Way Data Table – This video shows how to use a Two way data table to determine how changes in two spreadsheet inputs affect a single output.
  • Mortgage Data Table – 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.
  • Customer Value – 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.
  • Goal Seek – This video shows how Goal Seek can be used to determine the number of unit sales needed for a juice bar to break even.
  • Goal Seek Finance – 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.
  • Time to Breakeven – In this video we use Goal Seek to determine the number of weeks of sales needed to breakeven.
  • Scenario Manager – This video shows how the Scenario Manager can be used to describe how scenarios involving multiple spreadsheet inputs influence multiple spreadsheet outputs.
  • Down Payment – 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.
  • Project Management – This video shows how to use a One-Way data table to determine the critical activities in a project.
  • Hedging – This video shows how to use a One way data table to show how hedging with puts can reduce equity risk.
  • Bill Belichick’s Bold Move –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.
  • IF Statements and Data Tables – 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.
  • Data Table Chart – In this video we show how to create a chart to summarize the numbers in a two-way data table.
  • Hedge Funds – In this video we use a one-way data table to show how a simple hedge fund strategy neutralizes market risk.
  • The Syracuse Problem – In this video we use IF, MATCH, the ISEVEN function, and data tables to describe the famous Syracuse Problem conjecture
  • Relating Bond Price and Yield – In this video we use data tables to show the relationship between bond prices and yields.
  • Creating a Data Table Based on a Model in a Different Worksheet – In this video we show how to create data table based on a model in a different worksheet.

CONDITIONAL COUNTS, SUMS, AVERAGES, AND SUBTOTALS

This chapter covers Conditional Counts, Sums, Averages, and Subtotals and consists of 24 lessons.

  • COUNTIF and COUNTIFS Functions –In this video we show how the COUNTIF and COUNTIFS functions are used to do conditional counts on single or multiple criteria.
  • COUNT, COUNTA, and COUNTBLANK Functions –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.
  • SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS Functions –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.
  • SUMIFS With Duplicates –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.
  • Subtotals –This video shows how SUBTOTALS are used to determine how much each salesperson sold in each region.
  • The SUBTOTAL Function – In this video we show how the SUBTOTAL function can be used to perform calculations that ignore hidden rows or just include filtered rows.
  • Using The Subtotal Function To Count Cells With Colors – In this video we use the SUBTOTAL function to count the number of cells in a range that have a certain color.
  • Sorting Subtotals – In this video we show how you can sort Subtotals of revenue so the largest revenue shows on top.
  • Data Consolidate –This video shows how data consolidate is used to combine data from several workbooks to calculate total sales of each product during each month.
  • Summing Data in Different Worksheets –This video shows how to consolidate data in a single workbook.
  • Showing Data in Different Worksheets Side by Side –This video shows how to easily display the data in different worksheets in a side by side summry display.
  • Database Functions –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.
  • Computed Criteria –This video shows how Database functions are used with criteria based on an Excel formula.
  • DGET Function –This video shows how to use the DGET function to pull the location of a transaction based on the date and ID code.
  • Advanced Filter –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.
  • Conditional Counts and Sums With Non-Blanks – This video shows how to do conditional sums and counts where one criteria is that the cell does not contain a blank.
  • Benford’s Law – In this video we show (amazingly) that for 70% of all countries the first digit in their population is 1,2,3, or 4!
  • Finding the n’th Match –In this video we show how to find the n’th match to a word in a column.
  • Analyzing NFL Play Effectiveness –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. 
  • Using the Sumproduct Function for Conditional Sums and Counts –In this video we show how to use the amazing SUMPRODUCT function to calculate conditional counts and sums
  • How Many Winning Seasons? –In this video we use the amazing SUMPRODUCT function to count the number of winning and losing seasons the San Antonio Spurs had.
  • Using OR Criteria with Conditional Counts, Sums, and Averages –Usually conditional counts, sums, and averages utilize AND criteria. In this video we show how to utilize OR criteria with these functions.
  • Computing Sales – 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.
  • Ranked Choice Voting – In this video we use the COUNTIF and COUNTIFS functions to show how ranked choice voting works.

CONDITIONAL FORMATTING AND DATA VALIDATION

This chapter covers Conditional Formatting and Data Validation and consists of 14 lessons.

  • Top and Bottom Rules – This video shows how to highlight the 20 warmest temperatures since 1881 and all above average and below average temperatures since 1881.
  • Highlight Cells Rules – This video shows how to highlight duplicates, any cells containing a given text string, and highlighting cells based on criteria involving dates.
  • Data Bars, Colors Scales, and Icon Sets – This video shows how Data Bars, Color Scales, and Icon sets are used to summarize numerical data.
  • Formula Option Part One – 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.
  • Formula Option Part Two – This video continues to show how to conditional format based on a formula.
  • Stop if True – 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.
  • Data Validation – 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.
  • Custom Option – 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.
  • Using Data Validation To Prevent Entry Of Duplicates – In this video we show how to use data validation to prevent users from entering duplicate rows in a workbook
  • Circle Invalid Data – This video shows how to ensure that data not meeting a desired criteria is circled.
  • Highlighting New Hires – This video shows how to highlight employees who have just been hired.
  • Conditional Formatting and Sports Part One – In this video we show how to highlight all baseball players who are good on HR’s, Stolen Bases, and Batting Average.
  • Conditional Formatting and Sports Part Two – In this video we show how to use data bars, color scales, and icon sets to format sports data.
  • Conditional Formatting and Fraud Detection – In this video we show how conditional formatting can be used to perform many tests that are used to detect fraud in financial transaction data.

TABLES AND 3D FORMULAS

This chapter covers Tables and 3D Formulas and consists of 14 lessons.

  • Worksheet Templates – 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.
  • Three Dimensional Formula – This video shows how three dimensional formulas can be used to sum data across worksheets.
  • Three Dimensional Formulas With Wildcards – This video shows how to write 3D formulas that do calculations based on a worksheet name.
  • Paste Special – This video shows how the Paste Special Menu can be used to transpose data and divide “spread out cells” in a worksheet by 100
  • Tables Introduction – This video shows how making data a Table ensures that formulas update automatically when new data is added.
  • Table Graphs – In this video we show how making data a Table ensures that charts update automatically when new data is added.
  • Table Slicers – In this video we show how slicers can be used to “slice and dice” the data in Tables.
  • Table Extended – In this video we show how tables make drop down boxes, VLOOKUPS, SUMIF formulas and NPV calculations automatically update when new data is added.
  • Aggregate Function With Slicers – In this video we show how the AGGREGATE function is needed to ensure calculations based on Table Slicers are correct.
  • Conditional Formatting Tables – In this video we show how to make Conditional Formats based on Formulas update automatically when new data is added.
  • Copying Formulas Across Tables – This video shows how copying formulas across a table can be tricky.
  • Vignere Code – Inthis video we use VLOOKUP, ROW(), CHAR, MOD functions and the Table feature to encode and decode messages using the Vignere Code.
  • Filtering Charts With Table Slicers – In this video we show how to use Table Slicers to filter charts.

PIVOT TABLES

This chapter covers Pivot Tables and consists of 31 lessons.

  • Introduction to Pivot Tables – In this video we introduce PivotTables and show how to collapse and expand fields, sort data, and drill down to source data.
  • Analyzing Pivot Table 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.
  • Text Filters – In this video we show how text filters are used to filter a PivotTable based on text.
  • Grouping and Pivot Charts –In this video we show how to group data in a PivotTable and create a chart based on a PivotTable.
  • Value Field Settings and Show Values As -In this video we show how to summarize data as a Percentage of a Row or Column.
  • Calculated Fields –In this video we show how to create a calculated field based on PivotTable columns.
  • Slicers and Filters –In this video we show how slicers allow you to slice and dice sales by country and product.
  • GETPIVOTDATA Function –In this video we show how GETPIVOTDATA is used to extract data from a PivotTable for use in reports and charts.
  • Calculated Items –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.
  • Using Solve Order to Change Order of Calculations –In this video Solve Order is used to change the order in which Calculated Items are computed.
  • Timelines –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.
  • Analyzing Surveys and More Pivot Charts – In this video we show how to summarize a survey with a PivotTable and PivotChart.
  • Creating Pivot Tables From Multiple Ranges – In this video we show how to create PivotTables from data in multiple worksheets and/or workbooks.
  • Multiple Pivot Tables – In this video we show how to create many PivotTables based on a Filter.
  • Data Model – 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.
  • Pivot Tables and the NBA Part One – In this video we show how to use Pivot Tables to analyze NBA players.
  • Pivot Tables and the NBA Part Two – In this video we continue to show how Pivot Tables can be used to analyze NBA players.
  • Why Are There Fewer Births On Weekends? – In this video we show that the lack of C sections on weekends explains why there are fewer births on weekends.
  • Republican Congress – In this video we use PivotTables to show why Democrats get more votes for Congress yet more Republicans win.
  • PivotTables and Dating – In this video we show how to use PivotTables to analyze the dating preferences of men and women.
  • Simpsons Paradox – In this video we show how PivotTables illustrate the classical Simpson’s Paradox.
  • Putting it all Together – In this video we show how to use IF, VLOOKUP, PivotTables, and AVERAGEIFS functions to solve a tax accounting problem.
  • Election Demographics – In this video we use PivotTables and Slicers to analyze preferences of various demographic groups for Donald and Hillary.
  • Editing Pivot Table Default Features with Office 365 – In this video we show how subscribers to Office 365 can now easily update their Pivot Table defaults
  • Using Multiple Slicers with Pivot Tables – In this video we show how slicers can be set to control more than one PivotTable
  • Conditional Formatting and PivotTables – In this video we show how to apply Excel’s great Conditional Formatting capabilities to PivotTables
  • Pivot Tables and Unrecognized Dates – In this video we show how to group orders by month when Excel doesn’t like your date format.
  • Summarizing Bitcoin Data – In this video, using daily returns on bitcoin, we show how to summarize average returns by month, year and quarter of the year.
  • More on Calculated Fields – In this video we use calculated fields to determine total sales by year and fraction of sales each year that are promotional sales.
  • Using Custom Groups to Compute Net Promoter Score – In this video we use Custom Grouping in a Pivot Table to compute an important marketing metric, the Net Promoter Score.
  • Formatting a Single Pivot Table Cell and Moving a Pivot Table – In this video we show how in Excel 2019 and Office 365 you can move a Pivot Table and format a single cell in a Pivot Table.

DESCRIPTIVE STATISTICS

This chapter covers Descriptive Statistics and consists of 13 lessons.

  • Histogram Creation – In this video we show how to use a histogram to summarize Cisco’s monthly returns.
  • Histogram Shapes – In this video we discuss symmetric, positively, and negatively skewed histograms.
  • Descriptive Statistics – In this video we discuss measures of typical value, variation about the mean, and skewness.
  • Rule of Thumb – In this video we discuss how to determine normal variation for any data set.
  • More Statistical Functions – In this video we discuss the PERCENT, PERCENTRANK, LARGE, SMALL, and RANK functions.
  • Taking a Random Sample – In this video we show how to take a random sample size of 50 from 285 NBA players.
  • Congressional Apportionment – In this video we show how to fairly allocate members of the House of Representatives based on state populations.
  • Matchmaker – In this video we show how statistical functions and data tables can be used to find your “perfect match.”
  • Multiple Objective Decision Making – In this video we show how to choose a job based on multiple objectives.
  • Income Inequality – 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.
  • Descriptive Statistics for Grouped Data – In this video we show how to compute the mean, variance, and standard deviation for grouped data.
  • Chinese Accidental Deaths and Histograms – In this video we use a histogram to prove that Chinese provinces under-report accidental deaths.
  • Describing Baseball Salaries – In this video we show how to create a histogram based on text and use box plots and histograms to describe Major League Baseball Salaries.lessons

TREND CURVE, CORRELATION AND REGRESSION TO THE MEAN

This chapter covers Trend Curve, Correlation, and Regression to the Mean and consists of 13 lessons.

  • Linear Regression Part One – In this video we show how to find the line best fitting a set of bivariate data.
  • Linear Regression Part Two – In this video we show how the RSQ, SLOPE, INTERCEPT, and STEYX functions can be used to analyze a linear regression.
  • Exponential Growth – In this video we show how exponential growth can be used to fit bivariate data where the graph gets steeper.
  • Power and Demand Curves – In this video we show how to fit a constant elasticity or power demand curve.
  • Polynomial Demand Curves – In this video we show how to fit a quadratic demand curve based on 3 data points.
  • Learning Curves – 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.
  • Correlation – In this video we show how the concept of correlation can measure the strength of the linear association between monthly returns on six stocks.
  • Estimating the Beta of a Stock – In this video we show how the SLOPE function can be used to quickly estimate the Betas of many stocks.
  • Regression to the Mean – 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.
  • Moving Average – 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.
  • Finding a Good R Squared – In this video we show that an R Squared value of 0.99 is not necessarily that good.
  • Finding the Best Curve to Fit Data – In this video we show how to determine which of the linear, exponential, and power curve best fit a bi-variate relationship.
  • Converting ACT scores to SAT scores – In this video we use regression to show how to convert an ACT score to an SAT equivalent.

MULTIPLE REGRESSION

This chapter covers Multiple Regression and consists of 19 lessons.

  • Multiple Regression Part One – In this video we show how multiple regression is used to predict computer sales per-capita based on education and income.
  • Multiple Regression Part Two – In this video we continue from the previous example.
  • Dummy Variables – In this video we show how to incorporate qualitative variables such as seasonality to predict auto sales.
  • Introduction to Conjoint Analysis – In this video we show how conjoint analysis can determine what product attributes are valued by customers.
  • Conjoint Analysis and Regression – In this video we show how an actual regression is used to illustrate conjoint analysis.
  • Value Based Pricing –In this video we show how conjoint analysis is used to estimate the value of product attributes.
  • Nonlinearities and Interactions – In this video we show how to include nonlinearities and interactions in a multiple regression analysis.
  • What Makes NBA Teams Win – In this video we use multiple linear regression to show what makes NBA teams win.
  • QB Rating – In this video we show how to use multiple regression to closely approximate the NFL’s QB Rating formula.
  • Maximum Likelihood Estimation – In this video we show how Maximum Likelihood Estimation is used to estimate statistical parameters.
  • Logistic Regression – 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.)
  • Logistic Regression for Grouped Data – In this video we explain how to do logistic regression with grouped data
  • The Windchill Index – In this video we use multiple regression with nonlinearities and interactions to reverse engineer the complex Windchill Index formula.
  • Predicting Wine Prices – In this video we show how to use multiple regression to predict prices of Bordeaux wine based on temperature and rainfall.
  • One Way ANOVA – In this video we show how to perform a One-Way ANOVA.
  • Randomized Blocks – In this video we show how to perform a Two-Way ANOVA with no replication.
  • Two Way ANOVA Without Interaction – In this video we show how to analyze a Two-Way ANOVA with no interaction.
  • Two Way ANOVA With Interaction – In this video we show how to analyze a Two-Way ANOVA with interaction.
  • ANOVA With More Than Two Factors – 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 lessons.

  • Introduction to Probability – In this video we define the concept of an experiment, sample space, and solve some simple probability problems.
  • Addition Rule of Probability and Mutually Exclusive Events – In this video we define mutually exclusive events and show how to compute P(A or B) for events A and B.
  • The Rule of Complements – In this video we explain how to use the Rule of Complements to solve tricky probability problems.
  • Independent Events – In this video we define independent events and show how easy it is to compute P(A and B) for independent events.
  • Conditional Probability – In this video we show how to compute the probability of one event given that another event has occurred.
  • Total Probability – Leading up to Bayes theorem, In this video we develop the Law of Total Probability.
  • Bayes Theorem – In this video we show how Bayes Theorem allows us to use information to update probabilities.
  • Theoretical Basis for Naive Bayes – In this video we show how Bayes Rule can be used to efficiently classify observations into one of several groups
  • Calculating Likelihoods and Prior Probabilities for Naïve Bayes – In this video we calculate the prior probabilities and likelihoods needed to implement Naïve Bayes
  • Using Naïve Bayes to Compute Posterior Probabilities – 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 13 lessons.

  • Random Variables – In this video we show how to compute the mean, standard deviation, and variance of random variables.
  • Binomial Random Variables – In this video we show how to compute probabilities involving repeated trials with two outcomes (success or failure).
  • Binomial Mean Variance – In this video we discuss the mean, variance and standard deviation of a binomial random variable.
  • The Hypergeometric Random Variable – In this video we discuss how the Hypergeometric random variable can be used to calculate probabilities involving sampling without replacement.
  • The Negative Binomial Random Variable – In this video we show how to calculate probabilities involving the Negative Binomial Random Variable.
  • Poisson Random Variables – 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.
  • The Exponential Random Variable – In this video we show how to calculate probabilities involving the Exponential Random Variable.
  • Normal Random Variables – In this video we show how to compute probabilities for the normal random variable.
  • Central Limit Theorem – In this video we show how to use the normal random variable to approximate probabilities involving sums of >=30 random variables.
  • Z Scores – In this video we show how Z scores are used to measure how “unusual” a data point is.
  • The Weibull Random Variable – In this video we use the Weibull Random Variable to model machine lifetimes.
  • The Beta Random Variable – In this video we use the Beta random variable to model activity durations.
  • Analyzing a Plane Crash – In this video we use the binomial random variable to analyze how the chance of a single failing changes the likelihood of a 2 or 4 engine plane crashing.

ARRAY FORMULAS

This chapter covers Array Formulas and consists of 17 lessons.

  • Introduction to Array Formulas and Functions – In this video we show how array formulas and functions can fill more than one cell.
  • Simple Examples of Array Formulas – In this video we use simple examples to enhance your understanding of array formulas.
  • Array Formulas and Text Functions – 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.
  • Finding Smallest Numbers in a Range – In this video we show how to find the 6 smallest numbers in a range.
  • Emulating SUMIF and DSUM – In this video we show how array formulas can emulate the SUMIF and DSUM functions.
  • Emulating SUMIFS – In this video we show how an array formula can emulate the SUMIFS functions.
  • Emulating the OR Operator With Arrays – In this video we show how an array formula can select rows for a conditional sum based on an “OR” criteria.
  • Creating the MEDIANIF Function – In this video we use an array formula to create a MEDIANIF function.
  • Creating the STDEVIF Function – In this video we use an array formula to create a Standard Deviation If function.
  • Conditional Products – 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.
  • Finding Unique Entries in an Array – In this video we use an array formula to find all unique entries in an array.
  • Transpose Function – In this video we show how the TRANSPOSE function can be used to dynamically transpose a row of data into a column.
  • Frequency Function – 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.
  • Matrix Array Functions –In this video we show how to Multiply and Invert matrices.
  • Trend Function –In this video we show how to use multiple regression and the TREND array function to predict US Auto Sales.
  • Linest Function –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.
  • Fermat’s Little Theorem –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 21 lessons.

  • Offset Function –In this video we define the complex, powerful, and often misunderstood OFFSET function.
  • Left Hand Lookup –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.
  • Offset Cost –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.
  • Offset Account –In this video we show how the OFFSET, MATCH, and INDEX functions can analyze tax data imported from a PDF.
  • Most Recent Sales –In this video we show how the OFFSET function is used to return the most recent month of sales.
  • Dynamic Range –In this video we show how to create a dynamic range that automatically expands when new rows or columns of data are added.
  • Graphing the Last 6 Months of Sales – In this video we create a chart that always shows the last 6 months of sales.
  • Indirect Function – In this video we define the complex, powerful, and often misunderstood INDIRECT function.
  • Indirect Consolidate – 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.
  • Indirect With Spaces – In this video we show how to create worksheet cell combination addresses when the sheet names have spaces.
  • Indirect Formatting – In this video we show how to use the INDIRECT and ADDRESS functions to set up conditional formatting where you select a row heading and the formatting changes.
  • Indirect Range – In this video we use the INDIRECT function to efficiently copy formulas involving Range Names.
  • Worksheet Names – In this video we show how to easily find all the worksheet names in a workbook.
  • Nested Drop Down – 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.
  • Indirect Function and Stocks – 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.
  • Income Distribution Part One – In this video we begin to show how to use a country’s GINI coefficient (a measure of income inequality) to determine the entire distribution of a country’s income
  • Income Distribution Part Two – In this video we finish showing how to use a country’s GINI coefficient (a measure of income inequality) to determine the entire distribution of a country’s income
  • One Man One Vote? – In this video we explain the concept of “wasted votes” and show how this metric may determine the future of American politics
  • Average Points Scored – In this video we show how to find the average points scored by an NBA team during their last 5 games.
  • Technical Analysis – 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.
  • Using the INDEX Function to Return a Cell Address – In this video we show how the INDEX function can be used to return a cell address rather than a cell value.

OPTIMIZATION AND THE EXCEL SOLVER

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

  • Introduction to Solver Part One – 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.
  • Introduction to Solver Part Two – This video builds upon the previous video on Solver Optimization.
  • Introduction to Solver Part Three – This video builds upon the previous two videos on Solver Optimization.
  • Transportation – In this video we show how to find the cost minimizing distribution strategy that ships products from plants to customers.
  • Scheduling – In this video we show how to use the Excel Solver to find cost minimizing workforce schedules.
  • Capital Budgeting – In this video we show how to choose the subset of 20 projects that maximizes NPV subject to limited resources.
  • Fantasy Sports Part One – In this video we show how binary changing cells can be used to select Daily Fantasy Lineups.
  • Fantasy Sports Part Two – This video is a continuation of the previous Fantasy Sports Example.
  • Financial Solver – In this video we show how to use the Solver to solve for monthly mortgage payments and your needed annual retirement contribution.
  • NFL Point Spreads – In this video we show how to predict winners of NFL games based on past scores.
  • NFL Totals – In this video we show how to use scores of past NFL games to predict scores of future games.
  • Pricing Model – In this video we show how to estimate the demand for lipstick and use Solver to find a profit-maximizing price.
  • Warehouse Location – In this video we show how to locate one or two warehouses to minimize the distance shipments travel to customers.
  • Finding Distances – 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.
  • Demand Sensitivity to Price Change – In this video we show how to determine for a given percentage price change, the change in demand needed to break even.
  • Saving for 1 Year Salary – 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.
  • Sampling Plan For Attributes – 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.
  • The Assignment Problem – In this video we show how to use the Solver to assign school bus drivers to bus routes.
  • The Transshipment Problem – In this video we show how to minimize the cost of shipping a product from plants through warehouses to customers.
  • Inventory Model – In this video we show how to use solver to solve a simple inventory problem.
  • The Shortest Route Problem – In this video we show how to use the Solver to find the shortest route between two nodes.
  • Hypothesis Testing For a Population Mean – 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.
  • Asset Allocation – In this video we use the Solver to allocate an investment portfolio to gold, REITs, stocks, t-bills, and bonds.
  • Network Flow Model – In this video we show how to use the Solver to minimize the cost of sending material from one place to another.
  • Cheapest Diet – In this video we use the solver to find the cheapest diet of McDonald’s foods that meet various nutritional constraints
  • Calculating a Final Grade with Variable Weight on Tests – 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
  • Using Solver for Negotiation – 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 17 lessons.

  • Trend and Seasonality – In this video we show how to estimate the trend and seasonal indices for monthly US airline miles.
  • Ratio to Moving Average Part One – 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.
  • Ratio to Moving Average Part Two – In this video we conclude our discussion of the Ratio to Moving Average Method.
  • Winter’s Method of Forecasting Introduction – In this video we show how Winter’s exponential smoothing method can be used to forecast in the presence of Trend and seasonality.
  • Winter’s Method – Equations – This video is a continuation of the Winter’s Method of forecasting.
  • Winter’s Method – Initialization – This video is a continuation of the Winter’s Method of forecasting.
  • Winter’s Method – Implementation – This video is the implementation and conclusion of the Winter’s Method of forecasting.
  • Credit Union Forecasting Part One – In this video we show how to incorporate seasonality and special factors in a forecast of a bank’s daily customer count.
  • Credit Union Forecasting Part Two – In this video we finish our discussion of forecasting daily customer count at a bank.
  • Predicting Software Sales – In this video we show how to use the Excel Solver to determine how seasonality, PC sales, and launch dates affect software sales.
  • Using The Brier Score To Evaluate Forecasters – 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.)
  • Forecast Sheet – In this video we show how to use the Excel 2016 Forecast Sheet capability to forecast and develop confidence intervals for future values of a seasonal times series.
  • Forecast Functions – In this video we show how to use Excel 2016 Forecast Functions to forecast and develop confidence intervals for future values of a seasonal times series.
  • Forecasting Candy Bar Sales – 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.
  • Forecasting Google Trend Data – In this video we show how to download search data from Google Trends and use the Forecast Sheet to forecast future searches.
  • Evaluating Forecast Bias – In this video we show how to evaluate the bias in 538.com’s forecasts and find them to be unbiased.
  • Forecast Sheet Statistics – In this video we discuss Excel’s FORECAST.ETS.STAT function, which gives important statistics based on the forecasts created by the Forecast Sheet Tool.

CHARTING

This chapter covers Charting and consists of 32 lessons.

  • Combo and Secondary Axis Charts – In this video we create Combination charts and Charts with a Secondary Axis.
  • Handling Missing Data – In this video we show options for handling missing data in charts.
  • Handling Hidden Data – In this video we show options for handling hidden data in charts.
  • Adding Pictures to Column Charts – In this video we show how to add pictures to your column charts.
  • Data Labels – In this video we show how to add Data Labels to a column chart.
  • Data Tables – In this video we show how to add a Data Table below a column chart.
  • Labels From Cells – 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.
  • Creating Icon Sets – In this video we show how to use the WINGDINGS3 font to create customized Icon sets.
  • Band Charts – 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.
  • Thermometer Charts – In this video we show how to create Thermometer charts that summarize monthly performance against a target.
  • Dynamic Chart Labels – In this video we show how to create Chart Titles and Series Labels that dynamically update as we change our spreadsheet inputs.
  • Check Boxes and Chart Dashboards – In this video we show how to use CheckBoxes to control which series show in a chart.
  • List Boxes and Chart Dashboards –In this video we show how to use List Boxes to control which series shows in a chart.
  • Spinners –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.
  • Gantt Charts –In this video we show how to create a Gantt Chart that monitors completion times of project activities.
  • Dynamic Histograms –In this video we show how to create a dynamic histogram that updates automatically when new data is added.
  • Charts Based on Sorted Data –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.
  • Adding Conditional Colors to a Chart –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.
  • Waterfall Charts –In this video we show how to create Waterfall Charts with Excel 2013.
  • Creating Charts With GETPIVOTDATA –In this video we use GETPIVOTDATA and the Excel Table feature to create dynamic charts based on a PivotTable.
  • Pareto Charts –In this video we show how to create Pareto Charts in Excel 2013.
  • Vertical Line –In this video we show how to place a vertical line in a chart that moves when the chart is relocated.
  • Radar Charts –In this video we use Radar charts to summarize employee attributes on multiple dimensions.
  • Bubble Charts –In this video we use a Bubble Chart to summarize sales in different countries on three dimensions.
  • Sparklines Part One –In this video we use Sparklines to visually summarize daily customer counts in different bank branches in a single cell for each branch.
  • Sparklines Part Two –In this video we use Win Loss Sparklines to visually summarize week by week NFL results.
  • Adding a Picture to a Column Graph – In this video we show how to replace the bars in a column graph by a picture (in this case a soda bottle).
  • Stacking Pictures in a Column Graph – In this video we show how to stack pictures in a column graph so that, for example 1 plane = sales of 50 units.
  • Creating a Chart With a Secondary X and Y Axis – In this video we show how to create a chart with both a secondary x and y axis.
  • Pie Charts With a Bar Chart Breakout – In this video we show that starting with a pie chart, how to break out into a bar chart items with small values.
  • Deleting Points From a Chart – In this video we show how to delete selected points from a chart.
  • Highlighting the Best and Worst Months of Sales – In this video we show how to highlight the best and worst month of sales in a line chart.

KEYSTROKE SHORTCUTS AND HYPERLINKS

This chapter covers Keystroke Shortcuts and Hyperlinks and consists of 9 lessons.

  • Keystroke Formatting Shortcuts –In this video we show Keystroke shortcuts that are useful in formatting cells.
  • Worksheet Movement –In this video we show keystroke shortcuts that speed up worksheet movement.
  • Keystroke Formulas –In this video we show Keystroke shortcuts that help you better understand your spreadsheet’s formulas.
  • Cell Selection Shortcuts –In this video we show keystroke shortcuts that speed up selection of cells or worksheets.
  • Hyperlinks –In this video we show how to create Hyperlinks that enable you to click on the Hyperlink and move anywhere in your Workbook.
  • The HYPERLINK Function –In this video we show how the HYPERLINK function can be used to efficiently create hyperlinks to several websites or locations in a workbook.
  • Linking Excel Data to Word or Power Point –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.
  • Protecting a worksheet –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.
  • The Format Painter and F4 Key –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 lessons.

  • Introduction to Power View –In this video we introduce the cool charting capabilities of Power View by summarizing iced tea sales.
  • Power View Tiles –In this video we show how to use Tiles to chart tea sales in a selected state.
  • Power View Map –In this video we show how to use a map to summarize iced tea sales in each state.
  • Power View Multiples –In this video we show how Power View Multiples quickly create a separate chart summarizing sales of each product sold.
  • Power View Movies –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 15 lessons.

  • Introduction to Monte Carlo Simulation –In this video we introduce Monte Carlo Simulation. Monte Carlo can model the probability distribution of outcomes in the presence of uncertainty.
  • The RAND and RANDBETWEEN Functions –In this video we illustrate the RAND and RANDBETWEEN functions which are the key to Excel simulations.
  • Simulating a Discrete Random Variable –In this video we show how to simulate a discrete random variable.
  • Simulating a Normal Random Variable –In this video we show how to simulate a normal random variable.
  • Simulating a Baseball Inning –In this video we simulate the number of runs a baseball team scores in an inning.
  • The Birthday Problem –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.
  • Craps –In this video we show how to determine the probability of winning at craps.
  • The Newsperson Problem –In this video we show how to determine an order or production quantity that maximizes expected profit in the presence of uncertain demand.
  • Bidding –In this video we show how to determine a bid that maximizes expected profit given the uncertainty about competitor’s bidding behavior.
  • Poker –In this video we show how to “draw” a five card poker hand and calculate the chance of getting two pair.
  • Simulating the NBA Finals –In this video we show how simulation can be used to estimate the chance of each team winning the NBA finals.
  • Gambler’s Ruin –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?”
  • Stock Problem –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!
  • Winning Streak –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.
  • Analyzing the Neurotic Foul Shooter –In this video we show that a foul shooter’s chance of making a foul shot is equal to the fraction of fouls shots they have previously made. They make 1 of the first 2 foul shots. Given they make the 99th foul shot, what is the chance they make the 100th foul shot?

STATISTICAL QUALITY CONTROL

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

  • Process Capabilities –In this video we show how to determine the process capability of a process.
  • Six Sigma –In this video we show how Motorola determined that a process should have specifications be six sigma from the process mean.
  • P-Charts and Defective Units –In this video we show how P-Charts are used to determine if the fraction defective is in control.
  • X-Bar Charts and Process Control –In this video we show how to use an x-bar chart to determine if the level of a process is in control.
  • Ranges with R-Charts –In this video we show how to use an R-Chart to determine if a process’ variability is in control.
  • Moving Range Charts and Process Control –In this video we use a moving range chart to determine (based on individual measurements) if a process is in control.
  • Deming’s Funnel –In this video Deming’s funnel experiment is used to show why it is so important to determine if a process is in control.
  • Acceptance Sampling Part One –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.
  • Acceptance Sampling Part Two –In this video we show how to determine an optimal sampling plan when the population size is small.
  • The Taguchi Loss Function –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 24 lessons.

  • Simplex LP Solver Model – In this video we show how the Simplex LP solver finds an optimal solution.
  • GRG Solver Model – In this video we show how the GRG solver finds an optimal solution to “smooth” nonlinear optimization problems.
  • Evolutionary Solver Model – In this video we show how the EVOLUTIONARY solver finds an optimal solution to “non-smooth” nonlinear optimization problems.
  • Locating One Warehouse – In this video we show how the GRG Solver optimally locates a single warehouse.
  • Locating Two Warehouses – In this video we show how the GRG Multistart Solver optimally locates two warehouses.
  • Assigning Workers to Jobs Part One – In this video we show how the Evolutionary Solver uses penalties to solve optimization problems using non-smooth functions such as IF and COUNTIF.
  • Assigning Workers to Jobs Part Two – This video is the conclusion of the previous example of Assigning Workers to Jobs.
  • Scheduling John Deere – 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.
  • Traveling Salesperson – In this video we show how the Evolutionary Solver is used to solve sequencing problems.
  • Estimating Elasticities – In this video we use the GRG Multistart solver to estimate product and cross product elasticities.
  • Advanced Learning Curve – In this video we show how to fit customized learning curve models.
  • Bin Packing Problem – In this video we show how to use the Evolutionary Solver to solve the classic Bin Packing Problem.
  • Final Exam Scheduling – In this video we use the Evolutionary Solver to determine a final exam schedule that eliminates student conflicts.
  • Discriminant Analysis – 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.
  • Cluster Analysis –In this video we use the Evolutionary Solver to show that every US city is demographically similar to either LA, Memphis, SF, or Omaha.
  • Resolving Process Bottlenecks –In this video we use the evolutionary solver to find the minimum cost method of providing sufficient process capacity to meet demand.
  • The Toyota Manufacturing Sequence Algorithm –In this video we use the DIF capabilities of the Evolutionary Solver to implement the Toyota goal chasing method for scheduling car production.
  • Fast Food Scheduling –In this video we show how to use the Evolutionary Solver to schedule high school workers at a fast food restaurant
  • Solving Logic Puzzles Part One – In this video we use the Evolutionary Solver to solve two difficult logic puzzles.
  • Facility Layout – In this video we show how to locate hospital departments to minimize distance traveled.
  • Solving Logic Puzzles Part Two – In this video we use the Evolutionary Solver to solve two more difficult logic puzzles.
  • Pi Day Puzzle – In this video we pick a start date and end date at most two weeks apart and express dates as fractions (for example 4/5 is .80). What start and end dates makes the difference as close to Pi (3.14) as possible.
  • Introduction to Neural Networks – In this video we describe how neural networks work and sketch the approach needed to fit a neural network to data.
  • Fitting a Neural Network – In this video we use the GRG Multistart Solver to use a neural network to predict the Wind Chill Index. Our simple network accurately fits the complex wind chill equation.

NEGOTIATION

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

  • Two Person Negotiation Part One – In this video we show how to fairly divide property in a divorce settlement.
  • Two Person Negotiation Part Two – In this video we conclude our discussion of fairly dividing property in a divorce settlement
  • Why Maximize POP (Proportion of Potential) – In this video we show why it is reasonable to maximize POP (Proportion of Potential).
  • Store Contractor Negotiation Part One – In this video we show how a store and a construction contractor can negotiate on multiple issues.
  • Store Contractor Negotiation Part Two – In this video we continue our discussion of how a store and a construction contractor can negotiate on multiple issues.
  • Store Contractor Negotiation Part Three – In this video we conclude our discussion of how a store and a construction contractor can negotiate on multiple issues.
  • Three Player Negotiation – In this video we show how to allocate property among 3 participants.
  • Six Player Negotiation – In this video we show how 6 segments of Congress can compromise on difficult issues.
  • How to Determine Reservation Levels – 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 lessons.

  • Histograms – In this video we show how to use Excel 2016 to create much improved histograms.
  • Pareto Charts – In this video we show how to use Excel 2016 to create much improved Pareto charts.
  • Boxplots One Variable – In this video we show how to use Excel 2016 to create a Boxplot to summarize the results of the 1970 Draft Lottery.
  • Boxplots More Than One Variable – In this video we show how to use Excel 2016 to create a Boxplot that summarizes multiple test scores at three high schools.
  • Waterfall Chart – Time Series Data – In this video we show how to use an Excel 2016 Waterfall chart to summarize the progress of sales during a year.
  • Waterfall Chart – Revenue to Profit – In this video we show how to use an Excel 2016 Waterfall chart to summarize how various cost centers reduce Company Revenue and Profit.
  • Treemap Chart – Bookstore – In this video we use an Excel 2016 TreeMap or Mosaic chart to summarize hierarchical bookstore sales data.
  • Sunburst Chart – Bookstore – In this video we use an Excel 2016 Sunburst chart to summarize hierarchical bookstore sales data.
  • Sunburst Chart – Monthly Breakdown – In this video we use an Excel 2016 Sunburst chart to summarize quarterly and monthly sales data.
  • Treemap Chart -Restaurant – In this video we use the SUMIFS functions to prepare restaurant sales data and then summarize the sales data with a TreeMap chart.
  • Funnel Chart – In this video we show how to create a funnel chart which shows the winnowing of a pool of donors from prospects to bid donors.

INTERVIEW QUESTIONS

This chapter covers Interview Questions and consists of 15 lessons.

  • Uber – Interview Question Part One – In this video we determine the 10 hour time period in a two week period that has the most Uber pickups.
  • Uber – Percent of Calls Each Hour – In this video we breakdown the percentage of Uber calls by hour of the day.
  • Uber – Percent of Calls Each Hour and Date – In this video we breakdown for each day, the fraction of calls that occur during each hour.
  • Uber – Drivers Per Shift – In this video we determine the number of Uber drivers working each shift.
  • Changing Company Names – In this video we show how to change the old name for an airline to its current name.
  • Opening Lockers – In this video we solve the famous Wall Street Interview “Opening Locker” problem.
  • Magic Square – In this video we use the Evolutionary Solver capability to create a 3×3 Magic Square.
  • Wall Street Interview Question – In this video we show how to use the Excel Solver and MOD function to solve a classic Wall Street Interview Question.
  • Company Names – 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.
  • Putting Data In One Row Part One – In this video we show how to place an Olympic athlete’s information in a single row
  • Putting Data In One Row Part Two – 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
  • Summarizing Trends In The Summer Olympics – In this video sparklines are used to summarize trends in various attributes of the Summer Olympics
  • Formulas For The Microsoft Competition – In this video we show how to use Excel functions to answer interesting questions about the Men’s 2016 Olympic 200m race
  • More Fun With Gymnastic Formulas – In this video we use the INDEX/MATCH combination to answer many questions about Olympic Gymnastics
  • Using PivotTables To Analyze A 4X400M Relay – In this video we use PivotTables to analyze the Olympic 4x400M relay.

MACROS

This chapter covers Macros and consists of 10 lessons.
 
  • Inserting the Developer Tab – In this video we show how to make the Developer Tab appear on your ribbon.
  • Inserting a List of Names – 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.
  • Relative References With Macros – In this video we show how to use Relative References to make sure our list of names can be entered anywhere in our workbook.
  • Using Macros to Format Cells – 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).
  • Formatting a Range of Variable Size – In this video we show how to create a macro that formats a range of variable size.
  • Using a Macro to Freeze Values – In this video we create a macro that coverts formulas in a selected range to numerical values.
  • Using Macros For Reporting – 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.
  • Creating a Border With a Macro – In this video we show how to create a macro that places a border around a range of selected cells.
  • Personal Macro –In this video we discuss how to save a macro to your Personal Macro Workbook.
  • Sorting Macro –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 48 lessons.

  • Inventory on Time – In this video we use optimization to find a minimum cost production schedule that meets demand on time.
  • Inventory Modeling With Shortages – In this video we show how to create a linear solver model which allows for shortage costs as well as holding costs.
  • Aggregate Planning – In this video we extend our Solver Inventory model to account for hiring and firing workers.
  • Economic Order Quantity – In this video we discuss the famous EOQ formula, which recommends the cost minimizing order trading off holdings and ordering costs.
  • Power of Two – In this video we show how Power of Two ordering policies simplify the coordination of ordering when several products are involved.
  • Multiple Product Economic Order Quantity – In this video we show how coordinating ordering of multiple products can reduce supply chain costs.
  • Quantity Discounts – In this video we show how to determine the optimal order quantity when large order quantities result in quantity discounts.
  • Cycle Service Level – 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.
  • Fillrate, Reorder Point, and Safety Stock – In this video we define Fillrate and show how to determine a reorder point and safety stock that result in a desired Fillrate.
  • Transportation – In this video we show how to find the cost minimizing distribution strategy that ships products from plants to customers.
  • The Assignment Problem – In this video we show how to use the Solver to assign school bus drivers to bus routes.
  • The Shortest Route Problem – In this video we show how to use the Solver to find the shortest route between two nodes.
  • The Transshipment Problem – In this video we show how to minimize the cost of shipping a product from plants through warehouses to customers.
  • Network Flow Models – In this video we show how to use the Solver to minimize the cost of sending material from one place to another.
  • Locating One Warehouse – In this video we show how the GRG Solver optimally locates a single warehouse.
  • Locating Two Warehouses – In this video we show how the GRG Multistart Solver optimally locates two warehouses.
  • Finding Distances Between 100 Cities – In this video we use a two-way data table to find the distances between each pair of 100 world cities.
  • Locating Four Warehouses – In this video we locate 4 warehouses to optimally serve 100 cities.
  • Assigning Workers to Jobs Part One – In this video we show how the Evolutionary Solver uses penalties to solve optimization problems using non-smooth functions such as IF and COUNTIF.
  • Assigning Workers to Jobs Part Two – This video is the conclusion of the previous example of Assigning Workers to Jobs.
  • Scheduling John Deere – 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.
  • Traveling Salesperson – In this video we show how the Evolutionary Solver is used to solve sequencing problems.
  • The Vehicle Routing Problem – 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.
  • Vehicle Routing With Time Windows – In this video we solve the TSP when destinations must be visited during a specified time window.
  • Jobshop Scheduling – In this video we show how to schedule jobs to minimize number of late jobs and total days jobs are late.
  • The Toyota Manufacturing Sequence Algorithm – In this video we use the DIF capabilities of the Evolutionary Solver to implement the Toyota goal chasing method for scheduling car production.
  • Queuing – In this video we discuss how to compute the average number of people in line and the average time to spend waiting in line.
  • Inventory Turns – In this video we show how to use Little’s Law to compute a firm’s inventory turns.
  • Resolving Process Bottlenecks – In this video we use the evolutionary solver to find the minimum cost method of providing sufficient process capacity to meet demand.
  • Introduction to Monte Carlo Simulation – In this video we introduce Monte Carlo Simulation. Monte Carlo can model the probability distribution of outcomes in the presence of uncertainty.
  • The RAND and RANDBETWEEN Functions – In this video we illustrate the RAND and RANDBETWEEN functions which are the key to Excel simulations.
  • Simulating a Discrete Random Variable – In this video we show how to simulate a discrete random variable.
  • Simulating a Normal Random Variable – In this video we show how to simulate a normal random variable.
  • The Newsperson Problem – In this video we show how to determine an order or production quantity that maximizes expected profit in the presence of uncertain demand.
  • Facility Layout – In this video we show how to locate hospital departments to minimize distance traveled.
  • Location and Warehouse Shipments – In this video we show how to locate three warehouses and ship materials to minimize the total distance the shipments travel.
  • Maximizing Shipments Close to a Warehouse – 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.
  • How Many Warehouses are Needed to Provide Adequate Service? – In this video we determine the minimum number of warehouses needed to ensure that each shipment travels <=250 miles.
  • Solving Warehouse Location Problems With Limited Warehouse Capacity – In this video we show how to minimize the distance shipments travel when three warehouses each have limited capacity.
  • Supplier Selection – In this video we show how to determine weights for attributes that influence supplier selection.
  • Dynamic Capacity Planning – In this video we use a linear Solver model to determine how to add capacity to meet future requirements.
  • Estimating the Cost of Adding Capacity – In this video we explain why the cost of adding capacity is often not a linear function of the amount of capacity added.
  • Assembly Line Balancing – In this video we show how to use the Evolutionary Solver to solve Line balancing problems.
  • Flexible Capacity – In this video we show the importance of flexible capacity.
  • Project Management – In this video we show how to use simulation to estimate the length of time needed to complete a project.
  • The Math Behind The Goal – In this video we use simulation to show how variability reduces throughput and a bottleneck controls throughput.
  • The Bullwhip Effect – In this video we use simulation to show how variability of order quantities increases as you move upstream in a supply chain.
  • Estimating Censored Demand – 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 lessons.

  • The TEXTJOIN Function – In this video we show how the TEXTJOIN function can be used to make concatenation easier.
  • The MAXIFS and MINIFS Functions – In this video we show how to use the MAXIFS and MINFS Office 365 functions.
  • The IFS Function – In this video we show how the IFS Office 365 function simplifies the creation of nested IF formulas.
  • The SWITCH Function – In this video we show how the SWITCH function can be used as an alternative to the IFS or CHOOSE function.

POWER PIVOT

This chapter covers Power Pivot and has 13 lessons.

  • Introduction to PowerPivot – In this video we show how to use PowerPivot to combine data sources and create a PivotTable.
  • Creating PivotTables From a Database – In this video we show how to use PowerPivot to import tables from an Access database and create a PivotTable based on the imported data.
  • DAX Functions – In this video we show how to use DAX functions to add new columns to a PowerPivot page and use the new columns in a PivotTable.
  • The DISTINCTCOUNT Function – In this video we use PowerPivot to create a Pivottable that compares a company’s actual sales to their forecasted sales.
  • Comparing Forecasted Sales to Actual Sales – In this video we use PowerPivot to create a PivotTtable that compares a company’s actual sales to their forecasted sales.
  • The RELATED DAX Function – In this video we show how the RELATED DAX function is used to pull data from one data source tab to another data source tab
  • Pivot Tables Based on PowerPivot Calculated Columns – In this video we show how to create PivotTables based on PowerPivot calculated columns.
  • Calculated Measures – In this video we use the CALCULATE function to create calculated measures.
  • Key Performance Indicators – In this video we use a KPI to visually summarize a calculated measure.
  • Using Power Pivot to Draw a Random Sample – In this video we show how to use Power Pivot to take a random sample from a sales database.
  • Using Power Pivot to Sample From a Subset of a Database – In this video we use Power Pivot to take a random sample from only 2019 sales invoices.
  • Median and Date Intelligence DAX Functions – In this video we show how to use the MEDIAN DAX function to compute median sales and use the DATESMTD DAX function to compute sales to date during the current month.
  • Date Intelligence Functions Part II – In this video we show how date intelligence functions are used to determine sales on a given day a year ago and sales to date during the same month a year ago.

FILLED AND 3D MAPS

This chapter covers Filled and 3D Maps and has 6 lessons.

  • World Filled Maps – In this video we create a filled map showing sales in different countries.
  • US Filled Maps – In this video we create a 3D map that shows sales from different sources in different counties and cities.
  • Building a 3D Energy Map – In this video we create a 3D map that shows the energy capacity from different sources in each state.
  • 3D Time Animation – In this video we create a movie that shows how sales in each state change over time.
  • Mapping the 2016 Election – In this video we use a Filled map to show how Trump won the 2016 election by winning the “Flyover States.”
  • Mapping NYC Subway Lines – In this video we show how 3D Maps can be used to show the location of NYC subway stations.

DYNAMIC ARRAYS

This chapter covers Dynamic Arrays and has 10 lessons.

  • The Unique Function – In this video we show how the UNIQUE function extracts all unique entries across multiple columns.
  • The Unique Function and Tables – In this video we show that the UNIQUE function automatically updates with new data if the original data is in a table.
  • The SORT and SORTBY Functions – In this video we show how to use the SORT and SORTBY functions to sort arrays based on one or more columns.
  • The SORT and SORTBY Functions With Range Names and Tables – In this video we show how the SORT and SORTBY functions work with ranges and tables.
  • The FILTER Function – In this video we use the FILTER function to filter data based on single or multiple criteria.
  • The SEQUENCE Function – In this video we show how the SEQUENCE function can be used to insert a sequence of numbers with a constant increment in a range of cells.
  • The RANDARRAY Function – In this video we use the RANDARRAY function to generate a rectangular array of random numbers and also “shuffle” a deck of cards.
  • The #SPILL Operator – In this video we show  how using the # (Spill) operator, you can refer to the results of a  dynamic array formula in other formulas.
  • Dynamic Arrays and Nested Dropdowns – In this video we use Dynamic Arrays to create nested dropdown boxes. In the first dropdown you pick a college football conference. Then the 2nd dropdown just shows the teams in that conference.
  • Using Dynamic Arrays to Emulate a Pivot Table – In this video we show how to use Dynamic arrays to emulate a Pivot Table. When new sources of data are added, our calculations seamlessly update.

DATA TYPES

This chapter covers Data Types and has 5 lessons.

  • Introduction to Geography Data Types – In this video we show how Excel’s new geography data types let you create formulas that enter data about locations.
  • Geography Types and Excel Tables – In this video we show how Geography Data Types can be used with Excel Tables.
  • The FIELDVALUE Function – In this video we help you master the FIELDVALUE function which let’s you vary the property extracted for a location.
  • Resolving Ambiguity – In this video we show how to resolve ambiguity about the location of interest.
  • Stock Data Types – In this video you will learn how to use the new data types to extract data about a corporation.

POWER QUERY

This chapter covers Power Query and has 13 lessons.

  • Downloading Bitcoin Prices and Volumes – In this video we use Power Query to download Bitcoin price data and transform its appearance.
  • Downloading the Population of US Cities – In this video we use Power Query to download populations of the largest US cities and merge the city and state names in a single column.
  • Flattening a Table – In this video we use Power Query to flatten a table.
  • Turning a Flat File Into a Table – In this video we use Power Query to flatten a table.
  • Using a Formula to Add a Column – In this video we use an IF formula to compute a discounted price in a custom column.
  • Appending Queries – In this video we show how to append queries using Power Query.
  • Power Query Trick – In this video we show how you can combine all files in a folder and if you add new files the summary automatically updates when refreshed.
  • Filling Blanks in a Column – In this video we are given a list of NBA player statistics with most of the player’s teams missing. We use Power Query to fill in the missing team names and transpose the statistics from different columns to different rows.
  • Transforming Data Using Delimiters – In this video we split data at a comma and split the data into different rows. Then we split the data at a colon into two different columns.
  • Grouping Data with Power Query – In this video we show how to group sales data by region so that when new data is added, our calculations update to include calculations for new regions.
  • Using Power Query to Summarize Data Across Multiple Columns – In this video we show how to use Power Query to summarize sales by person and region. After refreshing, new data is automatically included in our summary.
  • Creating Custom Columns – In this video we create a custom column for revenue based on price and units sold and summarize revenue by year.
  • Pivoting Columns With Power Query – In this video our spreadsheet contains a single piece of data for a single player in each row. Each player has 5 rows of data and we show how to pivot the data so each player’s data appears in a single row and 5 columns.

BASIC FORMULAS INDEX, MATCH, AND LOOKUPS

This chapter covers Basic Formulas and consists of 43 lessons.
 
  • 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.
  • Bringing PDF Tables Into Excel – In this video we show how to import tables from a PDF file into Excel.
  • Multiplying 4 Consecutive Integers – In this video we use Excel formulas to demonstrate that whenever you multiply 4 consecutive integers and add 1 you get the square of an odd integer.
  • Explaining Google’s Page Rank – In this video we discuss the intuitive basis for Google’s Page Rank algorithm, which is used to rank web pages that are shown in response to search requests.
  • The XLOOKUP Function – In this video we introduce the incredibly powerful XLOOKUP function, which makes HLOOKUP and VLOOKUP functions obsolete!
  • XLOOKUP and the TRUE Argument – In this video we show how the XLOOKUP function can return multiple arguments when data for each item is in different rows (not columns) and how XLOOKUP can extend the VLOOKUP function with the TRUE argument.
  • XLOOKUP and the INDEX MATCH Combination – In this video we show how to use the XLOOKUP function to extend the INDEX MATCH combination.
  • Using XLOOKUP to reference a cell Range – In this video we show how XLOOKUP can be used to reference a range of cells in a table based on the start  and end row (or column) chosen.

FILTERING, SORTING, AND REMOVING DUPLICATES

This chapter covers Filtering, Sorting, and Removing Duplicates and consists of 16 lessons.

  • Basic Filters – This video shows how to filter based on selecting entries in a column.
  • Number Filters – This video shows how to filter based on numerical data.
  • Top 10 Filtering – This video shows how to pick out top 10, bottom 10, and the top 5% from a column.
  • Date Filters – This video shows how to filter based on dates.
  • Text Filters – This video shows how to filter based on text.
  • Color Filters – This video shows how to filter based on cell or font color.
  • Filtering a Column on Two Criteria – This video shows how to filter a single column based on text and cell color.
  • Basic Sorting – This video shows how to sort based on text, numbers, and dates.
  • Sorting on Colors – This video shows how to sort based on cell or font color.
  • Sorting A to Z – This video shows how to sort without using the sort dialog box.
  • Case Sensitive Sorting – This video shows how to sort with lower case entries coming before upper case entries.
  • Sorting With a Custom List – This video shows how to sort using a custom list.
  • Creating a Custom List – This video shows how to create a custom list and use it in sorting.
  • Sorting Across – This video shows how to sort across a row instead of a column.
  • Removing Duplicates – This video shows how to remove duplicates.
  • Filter by Selection – This video shows how you can filter a spreadsheet database simply by selecting items in the database. For example, selecting a player on the Boston Red Sox will filter the database to  show only members of the Red Sox.

RANGE NAMES

This chapter covers Range Names and consists of 9 lessons.

  • Name Box – This video shows how to create range names using the Name Box.
  • Create From Selection – 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.
  • F3 Trick – This video shows how to use the F3 key to paste a range name into a formula.
  • Name Entire Row or Column – This video shows how to name an entire row or column.
  • Apply Names – This video shows how to ensure that if you create range names, they will show up in previously created formulas.
  • Worksheet and Workbook Names – This video shows how you can create range names that apply to only a single worksheet or the entire workbook.
  • Range Name Tricks – This video shows how to use a named row in a formula and create ranges based on Excel formulas.
  • Implicit Intersection – This video shows how to pick off an entry in an array by simply typing the row and column headings.
  • Vertical Lookup With Range Names – This video shows how to use Range names in VLOOKUP formulas.

TEXT FUNCTIONS

This chapter covers Text Functions and consists of 15 lessons.

  • Left, Right, Mid, Len, and Concatenate Functions – This video shows how to use the LEFT, RIGHT, MID, LEN and CONCATENATE functions to manipulate text in a single cell.
  • Find Function and Text to Columns – This video shows how to use the FIND function and TEXT to COLUMNS to parse data in a single cell.
  • Histograms – This video shows how to use the REPT function to create a simple histogram.
  • Clean Function – This video shows how to Use the CLEAN, FIND, and SUBSTITUTE functions to remove invisible characters from your data.
  • Flash Fill – This video shows how to use FLASH FILL in Excel 2013 and 2016 to automatically perform the work of text functions.
  • Control + E and Flash Fill – In this video we show how Control + E makes Flash Fill perform better.
  • Finding the Last Word – This video shows how to use the SUBSTITUTE function to find the last word in a cell.
  • Two Column Lookup – This video shows how to use a VLOOKUP based on multiple columns of data.
  • Social Security Numbers – This video shows how to turn Social Security Numbers into PeopleSoft IDs.
  • Unicode – This video shows how to use Excel’s vast library of UNICODE characters.
  • Creating an Email List With Text Functions – This video shows how to take a list of email aliases and turn it into an email list.
  • The TEXT Function – 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.
  • The ROUND, ROUNDUP, ROUNDDOWN, and INT Functions – In this video we show how to use the ROUND, ROUNDUP, ROUNDDOWN, and INT functions to round numbers in any desired fashion.
  • Special Text Formats – In this video we show how to format zip codes, social security numbers, phone numbers, beginning zeroes, and trailing zeroes.
  • Using Flash Fill to Clean Messy Data – This video shows how to use Flash Fill to eliminate invisible characters. When using a VLOOKUP, invisible characters will return an error message.

AUDITING TOOLS, CIRCULAR REFERENCES, AND IF FUNCTIONS

This chapter covers Auditing Tools, Circular References, and IF Functions and consists of 24 lessons.

  • Auditing – 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.
  • Circular Reference – This video shows how circular references can be used and resolved.
  • IF Statements – This video shows how to use IF statements to calculate purchasing costs and analyze the game of craps.
  • Odds and Evens – This video explains how to use IF statements and the MOD function to determine the winner in odds and evens.
  • Annual Revenue – This video shows how to use IF statements to extract annual sales from quarterly sales data.
  • Product Life Cycle – This video shows how to use IF statements to model sales during the product life cycle.
  • Flatten Table – This video shows how to use IF statements to extract the data in a 20 row by 12 column table into 240 different rows.
  • Error Trap – 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.
  • NFL Scores – 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.
  • The ISERROR Function – Inthis 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.
  • Divide by 7 – This video shows how to determine if a check number like 2453589-6 has the sum of digits divisible by 7.
  • Valuation – This video shows how to use a two stage growth model to reverse engineer a stock price.
  • Combining Addresses – 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.
  • IF Statements And Sales Commission Calculations – This video shows how to use IF statements to compute sales commission based on a tiered commission structure.
  • IF Statements And Sales Commission Calculations Part Two – In this video we use VLOOKUP, MATCH, INDEX, And IF functions to model a complex sales incentive plan.
  • IF Statements And Option Investing – This video shows how IF statements can be used to model investment strategies involving options.
  • IF Statements Continued – In this video we use IF statements to model customer value, insurance premiums, and a person’s future salary.
  • Basic Shift Code – 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.
  • Rail Fence Code – In this video we show how to use IF Statements and Text Functions to Implement the Famous Rail Fence Code.
  • Longest Winning Streak – In this video we use IF statements to find the longest winning streak by the Washington Nationals during the 2016 season.
  • The Ducci Sequence – In this video we show how to use IF statements and the MATCH function to study the Ducci sequence
  • The Stern Sequence – In this video we use IF statements, the ISEVEN function, and the INDEX function to model the amazing Stern Sequence
  • How does waist to height ratio affect your longevity? – 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
  • Combining the IF and IFERROR Functions – In this video we show how to combine the IF and IFERROR functions in a single formula that is used to determine if a given school is an elementary school.

DATA TABLES, GOAL SEEK AND SCENARIO MANAGER

This chapter covers Data Tables, Goal Seek, and Scenario Manager and consists of 18 lessons.

  • One Way Data Table – This video shows how to use a One Way Data Table to determine how changing a single spreadsheet input affects multiple outputs.
  • Two Way Data Table – This video shows how to use a Two way data table to determine how changes in two spreadsheet inputs affect a single output.
  • Mortgage Data Table – 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.
  • Customer Value – 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.
  • Goal Seek – This video shows how Goal Seek can be used to determine the number of unit sales needed for a juice bar to break even.
  • Goal Seek Finance – 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.
  • Time to Breakeven – In this video we use Goal Seek to determine the number of weeks of sales needed to breakeven.
  • Scenario Manager – This video shows how the Scenario Manager can be used to describe how scenarios involving multiple spreadsheet inputs influence multiple spreadsheet outputs.
  • Down Payment – 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.
  • Project Management – This video shows how to use a One-Way data table to determine the critical activities in a project.
  • Hedging – This video shows how to use a One way data table to show how hedging with puts can reduce equity risk.
  • Bill Belichick’s Bold Move –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.
  • IF Statements and Data Tables – 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.
  • Data Table Chart – In this video we show how to create a chart to summarize the numbers in a two-way data table.
  • Hedge Funds – In this video we use a one-way data table to show how a simple hedge fund strategy neutralizes market risk.
  • The Syracuse Problem – In this video we use IF, MATCH, the ISEVEN function, and data tables to describe the famous Syracuse Problem conjecture
  • Relating Bond Price and Yield – In this video we use data tables to show the relationship between bond prices and yields.
  • Creating a Data Table Based on a Model in a Different Worksheet – In this video we show how to create data table based on a model in a different worksheet.

CONDITIONAL COUNTS, SUMS, AVERAGES, AND SUBTOTALS

This chapter covers Conditional Counts, Sums, Averages, and Subtotals and consists of 24 lessons.

  • COUNTIF and COUNTIFS Functions –In this video we show how the COUNTIF and COUNTIFS functions are used to do conditional counts on single or multiple criteria.
  • COUNT, COUNTA, and COUNTBLANK Functions –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.
  • SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS Functions –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.
  • SUMIFS With Duplicates –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.
  • Subtotals –This video shows how SUBTOTALS are used to determine how much each salesperson sold in each region.
  • The SUBTOTAL Function – In this video we show how the SUBTOTAL function can be used to perform calculations that ignore hidden rows or just include filtered rows.
  • Using The Subtotal Function To Count Cells With Colors – In this video we use the SUBTOTAL function to count the number of cells in a range that have a certain color.
  • Sorting Subtotals – In this video we show how you can sort Subtotals of revenue so the largest revenue shows on top.
  • Data Consolidate –This video shows how data consolidate is used to combine data from several workbooks to calculate total sales of each product during each month.
  • Summing Data in Different Worksheets –This video shows how to consolidate data in a single workbook.
  • Showing Data in Different Worksheets Side by Side –This video shows how to easily display the data in different worksheets in a side by side summry display.
  • Database Functions –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.
  • Computed Criteria –This video shows how Database functions are used with criteria based on an Excel formula.
  • DGET Function –This video shows how to use the DGET function to pull the location of a transaction based on the date and ID code.
  • Advanced Filter –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.
  • Conditional Counts and Sums With Non-Blanks – This video shows how to do conditional sums and counts where one criteria is that the cell does not contain a blank.
  • Benford’s Law – In this video we show (amazingly) that for 70% of all countries the first digit in their population is 1,2,3, or 4!
  • Finding the n’th Match –In this video we show how to find the n’th match to a word in a column.
  • Analyzing NFL Play Effectiveness –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. 
  • Using the Sumproduct Function for Conditional Sums and Counts –In this video we show how to use the amazing SUMPRODUCT function to calculate conditional counts and sums
  • How Many Winning Seasons? –In this video we use the amazing SUMPRODUCT function to count the number of winning and losing seasons the San Antonio Spurs had.
  • Using OR Criteria with Conditional Counts, Sums, and Averages –Usually conditional counts, sums, and averages utilize AND criteria. In this video we show how to utilize OR criteria with these functions.
  • Computing Sales – 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.
  • Ranked Choice Voting – In this video we use the COUNTIF and COUNTIFS functions to show how ranked choice voting works.

CONDITIONAL FORMATTING AND DATA VALIDATION

This chapter covers Conditional Formatting and Data Validation and consists of 14 lessons.

  • Top and Bottom Rules – This video shows how to highlight the 20 warmest temperatures since 1881 and all above average and below average temperatures since 1881.
  • Highlight Cells Rules – This video shows how to highlight duplicates, any cells containing a given text string, and highlighting cells based on criteria involving dates.
  • Data Bars, Colors Scales, and Icon Sets – This video shows how Data Bars, Color Scales, and Icon sets are used to summarize numerical data.
  • Formula Option Part One – 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.
  • Formula Option Part Two – This video continues to show how to conditional format based on a formula.
  • Stop if True – 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.
  • Data Validation – 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.
  • Custom Option – 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.
  • Using Data Validation To Prevent Entry Of Duplicates – In this video we show how to use data validation to prevent users from entering duplicate rows in a workbook
  • Circle Invalid Data – This video shows how to ensure that data not meeting a desired criteria is circled.
  • Highlighting New Hires – This video shows how to highlight employees who have just been hired.
  • Conditional Formatting and Sports Part One – In this video we show how to highlight all baseball players who are good on HR’s, Stolen Bases, and Batting Average.
  • Conditional Formatting and Sports Part Two – In this video we show how to use data bars, color scales, and icon sets to format sports data.
  • Conditional Formatting and Fraud Detection – In this video we show how conditional formatting can be used to perform many tests that are used to detect fraud in financial transaction data.

TABLES AND 3D FORMULAS

This chapter covers Tables and 3D Formulas and consists of 14 lessons.

  • Worksheet Templates – 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.
  • Three Dimensional Formula – This video shows how three dimensional formulas can be used to sum data across worksheets.
  • Three Dimensional Formulas With Wildcards – This video shows how to write 3D formulas that do calculations based on a worksheet name.
  • Paste Special – This video shows how the Paste Special Menu can be used to transpose data and divide “spread out cells” in a worksheet by 100
  • Tables Introduction – This video shows how making data a Table ensures that formulas update automatically when new data is added.
  • Table Graphs – In this video we show how making data a Table ensures that charts update automatically when new data is added.
  • Table Slicers – In this video we show how slicers can be used to “slice and dice” the data in Tables.
  • Table Extended – In this video we show how tables make drop down boxes, VLOOKUPS, SUMIF formulas and NPV calculations automatically update when new data is added.
  • Aggregate Function With Slicers – In this video we show how the AGGREGATE function is needed to ensure calculations based on Table Slicers are correct.
  • Conditional Formatting Tables – In this video we show how to make Conditional Formats based on Formulas update automatically when new data is added.
  • Copying Formulas Across Tables – This video shows how copying formulas across a table can be tricky.
  • Vignere Code – Inthis video we use VLOOKUP, ROW(), CHAR, MOD functions and the Table feature to encode and decode messages using the Vignere Code.
  • Filtering Charts With Table Slicers – In this video we show how to use Table Slicers to filter charts.

PIVOT TABLES

This chapter covers Pivot Tables and consists of 31 lessons.

  • Introduction to Pivot Tables – In this video we introduce PivotTables and show how to collapse and expand fields, sort data, and drill down to source data.
  • Analyzing Pivot Table 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.
  • Text Filters – In this video we show how text filters are used to filter a PivotTable based on text.
  • Grouping and Pivot Charts –In this video we show how to group data in a PivotTable and create a chart based on a PivotTable.
  • Value Field Settings and Show Values As -In this video we show how to summarize data as a Percentage of a Row or Column.
  • Calculated Fields –In this video we show how to create a calculated field based on PivotTable columns.
  • Slicers and Filters –In this video we show how slicers allow you to slice and dice sales by country and product.
  • GETPIVOTDATA Function –In this video we show how GETPIVOTDATA is used to extract data from a PivotTable for use in reports and charts.
  • Calculated Items –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.
  • Using Solve Order to Change Order of Calculations –In this video Solve Order is used to change the order in which Calculated Items are computed.
  • Timelines –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.
  • Analyzing Surveys and More Pivot Charts – In this video we show how to summarize a survey with a PivotTable and PivotChart.
  • Creating Pivot Tables From Multiple Ranges – In this video we show how to create PivotTables from data in multiple worksheets and/or workbooks.
  • Multiple Pivot Tables – In this video we show how to create many PivotTables based on a Filter.
  • Data Model – 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.
  • Pivot Tables and the NBA Part One – In this video we show how to use Pivot Tables to analyze NBA players.
  • Pivot Tables and the NBA Part Two – In this video we continue to show how Pivot Tables can be used to analyze NBA players.
  • Why Are There Fewer Births On Weekends? – In this video we show that the lack of C sections on weekends explains why there are fewer births on weekends.
  • Republican Congress – In this video we use PivotTables to show why Democrats get more votes for Congress yet more Republicans win.
  • PivotTables and Dating – In this video we show how to use PivotTables to analyze the dating preferences of men and women.
  • Simpsons Paradox – In this video we show how PivotTables illustrate the classical Simpson’s Paradox.
  • Putting it all Together – In this video we show how to use IF, VLOOKUP, PivotTables, and AVERAGEIFS functions to solve a tax accounting problem.
  • Election Demographics – In this video we use PivotTables and Slicers to analyze preferences of various demographic groups for Donald and Hillary.
  • Editing Pivot Table Default Features with Office 365 – In this video we show how subscribers to Office 365 can now easily update their Pivot Table defaults
  • Using Multiple Slicers with Pivot Tables – In this video we show how slicers can be set to control more than one PivotTable
  • Conditional Formatting and PivotTables – In this video we show how to apply Excel’s great Conditional Formatting capabilities to PivotTables
  • Pivot Tables and Unrecognized Dates – In this video we show how to group orders by month when Excel doesn’t like your date format.
  • Summarizing Bitcoin Data – In this video, using daily returns on bitcoin, we show how to summarize average returns by month, year and quarter of the year.
  • More on Calculated Fields – In this video we use calculated fields to determine total sales by year and fraction of sales each year that are promotional sales.
  • Using Custom Groups to Compute Net Promoter Score – In this video we use Custom Grouping in a Pivot Table to compute an important marketing metric, the Net Promoter Score.
  • Formatting a Single Pivot Table Cell and Moving a Pivot Table – In this video we show how in Excel 2019 and Office 365 you can move a Pivot Table and format a single cell in a Pivot Table.

DESCRIPTIVE STATISTICS

This chapter covers Descriptive Statistics and consists of 13 lessons.

  • Histogram Creation – In this video we show how to use a histogram to summarize Cisco’s monthly returns.
  • Histogram Shapes – In this video we discuss symmetric, positively, and negatively skewed histograms.
  • Descriptive Statistics – In this video we discuss measures of typical value, variation about the mean, and skewness.
  • Rule of Thumb – In this video we discuss how to determine normal variation for any data set.
  • More Statistical Functions – In this video we discuss the PERCENT, PERCENTRANK, LARGE, SMALL, and RANK functions.
  • Taking a Random Sample – In this video we show how to take a random sample size of 50 from 285 NBA players.
  • Congressional Apportionment – In this video we show how to fairly allocate members of the House of Representatives based on state populations.
  • Matchmaker – In this video we show how statistical functions and data tables can be used to find your “perfect match.”
  • Multiple Objective Decision Making – In this video we show how to choose a job based on multiple objectives.
  • Income Inequality – 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.
  • Descriptive Statistics for Grouped Data – In this video we show how to compute the mean, variance, and standard deviation for grouped data.
  • Chinese Accidental Deaths and Histograms – In this video we use a histogram to prove that Chinese provinces under-report accidental deaths.
  • Describing Baseball Salaries – In this video we show how to create a histogram based on text and use box plots and histograms to describe Major League Baseball Salaries.lessons

TREND CURVE, CORRELATION AND REGRESSION TO THE MEAN

This chapter covers Trend Curve, Correlation, and Regression to the Mean and consists of 13 lessons.

  • Linear Regression Part One – In this video we show how to find the line best fitting a set of bivariate data.
  • Linear Regression Part Two – In this video we show how the RSQ, SLOPE, INTERCEPT, and STEYX functions can be used to analyze a linear regression.
  • Exponential Growth – In this video we show how exponential growth can be used to fit bivariate data where the graph gets steeper.
  • Power and Demand Curves – In this video we show how to fit a constant elasticity or power demand curve.
  • Polynomial Demand Curves – In this video we show how to fit a quadratic demand curve based on 3 data points.
  • Learning Curves – 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.
  • Correlation – In this video we show how the concept of correlation can measure the strength of the linear association between monthly returns on six stocks.
  • Estimating the Beta of a Stock – In this video we show how the SLOPE function can be used to quickly estimate the Betas of many stocks.
  • Regression to the Mean – 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.
  • Moving Average – 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.
  • Finding a Good R Squared – In this video we show that an R Squared value of 0.99 is not necessarily that good.
  • Finding the Best Curve to Fit Data – In this video we show how to determine which of the linear, exponential, and power curve best fit a bi-variate relationship.
  • Converting ACT scores to SAT scores – In this video we use regression to show how to convert an ACT score to an SAT equivalent.

MULTIPLE REGRESSION

This chapter covers Multiple Regression and consists of 19 lessons.

  • Multiple Regression Part One – In this video we show how multiple regression is used to predict computer sales per-capita based on education and income.
  • Multiple Regression Part Two – In this video we continue from the previous example.
  • Dummy Variables – In this video we show how to incorporate qualitative variables such as seasonality to predict auto sales.
  • Introduction to Conjoint Analysis – In this video we show how conjoint analysis can determine what product attributes are valued by customers.
  • Conjoint Analysis and Regression – In this video we show how an actual regression is used to illustrate conjoint analysis.
  • Value Based Pricing –In this video we show how conjoint analysis is used to estimate the value of product attributes.
  • Nonlinearities and Interactions – In this video we show how to include nonlinearities and interactions in a multiple regression analysis.
  • What Makes NBA Teams Win – In this video we use multiple linear regression to show what makes NBA teams win.
  • QB Rating – In this video we show how to use multiple regression to closely approximate the NFL’s QB Rating formula.
  • Maximum Likelihood Estimation – In this video we show how Maximum Likelihood Estimation is used to estimate statistical parameters.
  • Logistic Regression – 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.)
  • Logistic Regression for Grouped Data – In this video we explain how to do logistic regression with grouped data
  • The Windchill Index – In this video we use multiple regression with nonlinearities and interactions to reverse engineer the complex Windchill Index formula.
  • Predicting Wine Prices – In this video we show how to use multiple regression to predict prices of Bordeaux wine based on temperature and rainfall.
  • One Way ANOVA – In this video we show how to perform a One-Way ANOVA.
  • Randomized Blocks – In this video we show how to perform a Two-Way ANOVA with no replication.
  • Two Way ANOVA Without Interaction – In this video we show how to analyze a Two-Way ANOVA with no interaction.
  • Two Way ANOVA With Interaction – In this video we show how to analyze a Two-Way ANOVA with interaction.
  • ANOVA With More Than Two Factors – In this video we show how to determine how 3 or more factors can be set to optimize a dependent variable.

OFFSET AND INDIRECT FUNCTIONS

This chapter covers the Offset and Indirect Functions and consists of 21 lessons.

  • Offset Function –In this video we define the complex, powerful, and often misunderstood OFFSET function.
  • Left Hand Lookup –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.
  • Offset Cost –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.
  • Offset Account –In this video we show how the OFFSET, MATCH, and INDEX functions can analyze tax data imported from a PDF.
  • Most Recent Sales –In this video we show how the OFFSET function is used to return the most recent month of sales.
  • Dynamic Range –In this video we show how to create a dynamic range that automatically expands when new rows or columns of data are added.
  • Graphing the Last 6 Months of Sales – In this video we create a chart that always shows the last 6 months of sales.
  • Indirect Function – In this video we define the complex, powerful, and often misunderstood INDIRECT function.
  • Indirect Consolidate – 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.
  • Indirect With Spaces – In this video we show how to create worksheet cell combination addresses when the sheet names have spaces.
  • Indirect Formatting – In this video we show how to use the INDIRECT and ADDRESS functions to set up conditional formatting where you select a row heading and the formatting changes.
  • Indirect Range – In this video we use the INDIRECT function to efficiently copy formulas involving Range Names.
  • Worksheet Names – In this video we show how to easily find all the worksheet names in a workbook.
  • Nested Drop Down – 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.
  • Indirect Function and Stocks – 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.
  • Income Distribution Part One – In this video we begin to show how to use a country’s GINI coefficient (a measure of income inequality) to determine the entire distribution of a country’s income
  • Income Distribution Part Two – In this video we finish showing how to use a country’s GINI coefficient (a measure of income inequality) to determine the entire distribution of a country’s income
  • One Man One Vote? – In this video we explain the concept of “wasted votes” and show how this metric may determine the future of American politics
  • Average Points Scored – In this video we show how to find the average points scored by an NBA team during their last 5 games.
  • Technical Analysis – 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.
  • Using the INDEX Function to Return a Cell Address – In this video we show how the INDEX function can be used to return a cell address rather than a cell value.

OPTIMIZATION AND THE EXCEL SOLVER

This chapter covers Optimization and the Excel Solver and consists of 4 lessons.

  • Introduction to Solver Part One – 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.
  • Introduction to Solver Part Two – This video builds upon the previous video on Solver Optimization.
  • Pricing Model – In this video we show how to estimate the demand for lipstick and use Solver to find a profit-maximizing price.
  • Demand Sensitivity to Price Change – In this video we show how to determine for a given percentage price change, the change in demand needed to break even.

NEGOTIATION

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

  • Two Person Negotiation Part One – In this video we show how to fairly divide property in a divorce settlement.
  • Two Person Negotiation Part Two – In this video we conclude our discussion of fairly dividing property in a divorce settlement
  • Why Maximize POP (Proportion of Potential) – In this video we show why it is reasonable to maximize POP (Proportion of Potential).
  • Store Contractor Negotiation Part One – In this video we show how a store and a construction contractor can negotiate on multiple issues.
  • Store Contractor Negotiation Part Two – In this video we continue our discussion of how a store and a construction contractor can negotiate on multiple issues.
  • Store Contractor Negotiation Part Three – In this video we conclude our discussion of how a store and a construction contractor can negotiate on multiple issues.
  • Three Player Negotiation – In this video we show how to allocate property among 3 participants.
  • Six Player Negotiation – In this video we show how 6 segments of Congress can compromise on difficult issues.
  • How to Determine Reservation Levels – In this video we show how to determine a person’s level of satisfaction if negotiations fall through.

ARRAY FORMULAS

This chapter covers Array Formulas and consists of 17 lessons.

  • Introduction to Array Formulas and Functions – In this video we show how array formulas and functions can fill more than one cell.
  • Simple Examples of Array Formulas – In this video we use simple examples to enhance your understanding of array formulas.
  • Array Formulas and Text Functions – 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.
  • Finding Smallest Numbers in a Range – In this video we show how to find the 6 smallest numbers in a range.
  • Emulating SUMIF and DSUM – In this video we show how array formulas can emulate the SUMIF and DSUM functions.
  • Emulating SUMIFS – In this video we show how an array formula can emulate the SUMIFS functions.
  • Emulating the OR Operator With Arrays – In this video we show how an array formula can select rows for a conditional sum based on an “OR” criteria.
  • Creating the MEDIANIF Function – In this video we use an array formula to create a MEDIANIF function.
  • Creating the STDEVIF Function – In this video we use an array formula to create a Standard Deviation If function.
  • Conditional Products – 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.
  • Finding Unique Entries in an Array – In this video we use an array formula to find all unique entries in an array.
  • Transpose Function – In this video we show how the TRANSPOSE function can be used to dynamically transpose a row of data into a column.
  • Frequency Function – 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.
  • Matrix Array Functions –In this video we show how to Multiply and Invert matrices.
  • Trend Function –In this video we show how to use multiple regression and the TREND array function to predict US Auto Sales.
  • Linest Function –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.
  • Fermat’s Little Theorem –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.

FORECASTING

This chapter covers Forecasting and consists of 17 lessons.

  • Trend and Seasonality – In this video we show how to estimate the trend and seasonal indices for monthly US airline miles.
  • Ratio to Moving Average Part One – 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.
  • Ratio to Moving Average Part Two – In this video we conclude our discussion of the Ratio to Moving Average Method.
  • Winter’s Method of Forecasting Introduction – In this video we show how Winter’s exponential smoothing method can be used to forecast in the presence of Trend and seasonality.
  • Winter’s Method – Equations – This video is a continuation of the Winter’s Method of forecasting.
  • Winter’s Method – Initialization – This video is a continuation of the Winter’s Method of forecasting.
  • Winter’s Method – Implementation – This video is the implementation and conclusion of the Winter’s Method of forecasting.
  • Credit Union Forecasting Part One – In this video we show how to incorporate seasonality and special factors in a forecast of a bank’s daily customer count.
  • Credit Union Forecasting Part Two – In this video we finish our discussion of forecasting daily customer count at a bank.
  • Predicting Software Sales – In this video we show how to use the Excel Solver to determine how seasonality, PC sales, and launch dates affect software sales.
  • Using The Brier Score To Evaluate Forecasters – 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.)
  • Forecast Sheet – In this video we show how to use the Excel 2016 Forecast Sheet capability to forecast and develop confidence intervals for future values of a seasonal times series.
  • Forecast Functions – In this video we show how to use Excel 2016 Forecast Functions to forecast and develop confidence intervals for future values of a seasonal times series.
  • Forecasting Candy Bar Sales – 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.
  • Forecasting Google Trend Data – In this video we show how to download search data from Google Trends and use the Forecast Sheet to forecast future searches.
  • Evaluating Forecast Bias – In this video we show how to evaluate the bias in 538.com’s forecasts and find them to be unbiased.
  • Forecast Sheet Statistics – In this video we discuss Excel’s FORECAST.ETS.STAT function, which gives important statistics based on the forecasts created by the Forecast Sheet Tool.

CHARTING

This chapter covers Charting and consists of 32 lessons.

  • Combo and Secondary Axis Charts – In this video we create Combination charts and Charts with a Secondary Axis.
  • Handling Missing Data – In this video we show options for handling missing data in charts.
  • Handling Hidden Data – In this video we show options for handling hidden data in charts.
  • Adding Pictures to Column Charts – In this video we show how to add pictures to your column charts.
  • Data Labels – In this video we show how to add Data Labels to a column chart.
  • Data Tables – In this video we show how to add a Data Table below a column chart.
  • Labels From Cells – 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.
  • Creating Icon Sets – In this video we show how to use the WINGDINGS3 font to create customized Icon sets.
  • Band Charts – 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.
  • Thermometer Charts – In this video we show how to create Thermometer charts that summarize monthly performance against a target.
  • Dynamic Chart Labels – In this video we show how to create Chart Titles and Series Labels that dynamically update as we change our spreadsheet inputs.
  • Check Boxes and Chart Dashboards – In this video we show how to use CheckBoxes to control which series show in a chart.
  • List Boxes and Chart Dashboards –In this video we show how to use List Boxes to control which series shows in a chart.
  • Spinners –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.
  • Gantt Charts –In this video we show how to create a Gantt Chart that monitors completion times of project activities.
  • Dynamic Histograms –In this video we show how to create a dynamic histogram that updates automatically when new data is added.
  • Charts Based on Sorted Data –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.
  • Adding Conditional Colors to a Chart –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.
  • Waterfall Charts –In this video we show how to create Waterfall Charts with Excel 2013.
  • Creating Charts With GETPIVOTDATA –In this video we use GETPIVOTDATA and the Excel Table feature to create dynamic charts based on a PivotTable.
  • Pareto Charts –In this video we show how to create Pareto Charts in Excel 2013.
  • Vertical Line –In this video we show how to place a vertical line in a chart that moves when the chart is relocated.
  • Radar Charts –In this video we use Radar charts to summarize employee attributes on multiple dimensions.
  • Bubble Charts –In this video we use a Bubble Chart to summarize sales in different countries on three dimensions.
  • Sparklines Part One –In this video we use Sparklines to visually summarize daily customer counts in different bank branches in a single cell for each branch.
  • Sparklines Part Two –In this video we use Win Loss Sparklines to visually summarize week by week NFL results.
  • Adding a Picture to a Column Graph – In this video we show how to replace the bars in a column graph by a picture (in this case a soda bottle).
  • Stacking Pictures in a Column Graph – In this video we show how to stack pictures in a column graph so that, for example 1 plane = sales of 50 units.
  • Creating a Chart With a Secondary X and Y Axis – In this video we show how to create a chart with both a secondary x and y axis.
  • Pie Charts With a Bar Chart Breakout – In this video we show that starting with a pie chart, how to break out into a bar chart items with small values.
  • Deleting Points From a Chart – In this video we show how to delete selected points from a chart.
  • Highlighting the Best and Worst Months of Sales – In this video we show how to highlight the best and worst month of sales in a line chart.

ADVANCED SOLVER

This chapter covers Advanced Solver and consists of 5 lessons.

 

  • Discriminant Analysis – 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.
  • Cluster Analysis –In this video we use the Evolutionary Solver to show that every US city is demographically similar to either LA, Memphis, SF, or Omaha.
  • Estimating Elasticities  –In this video we use the GRG Multistart solver to estimate product and cross product elasticities.
  • Introduction to Neural Networks – In this video we describe how neural networks work and sketch the approach needed to fit a neural network to data.
  • Fitting a Neural Network – In this video we use the GRG Multistart Solver to use a neural network to predict the Wind Chill Index. Our simple network accurately fits the complex wind chill equation.

EXCEL 2016 CHARTS

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

  • Histograms – In this video we show how to use Excel 2016 to create much improved histograms.
  • Pareto Charts – In this video we show how to use Excel 2016 to create much improved Pareto charts.
  • Boxplots One Variable – In this video we show how to use Excel 2016 to create a Boxplot to summarize the results of the 1970 Draft Lottery.
  • Boxplots More Than One Variable – In this video we show how to use Excel 2016 to create a Boxplot that summarizes multiple test scores at three high schools.
  • Waterfall Chart – Time Series Data – In this video we show how to use an Excel 2016 Waterfall chart to summarize the progress of sales during a year.
  • Waterfall Chart – Revenue to Profit – In this video we show how to use an Excel 2016 Waterfall chart to summarize how various cost centers reduce Company Revenue and Profit.
  • Treemap Chart – Bookstore – In this video we use an Excel 2016 TreeMap or Mosaic chart to summarize hierarchical bookstore sales data.
  • Sunburst Chart – Bookstore – In this video we use an Excel 2016 Sunburst chart to summarize hierarchical bookstore sales data.
  • Sunburst Chart – Monthly Breakdown – In this video we use an Excel 2016 Sunburst chart to summarize quarterly and monthly sales data.
  • Treemap Chart -Restaurant – In this video we use the SUMIFS functions to prepare restaurant sales data and then summarize the sales data with a TreeMap chart.
  • Funnel Chart – In this video we show how to create a funnel chart which shows the winnowing of a pool of donors from prospects to bid donors.

OFFICE 365 FUNCTIONS

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

  • The TEXTJOIN Function – In this video we show how the TEXTJOIN function can be used to make concatenation easier.
  • The MAXIFS and MINIFS Functions – In this video we show how to use the MAXIFS and MINFS Office 365 functions.
  • The IFS Function – In this video we show how the IFS Office 365 function simplifies the creation of nested IF formulas.
  • The SWITCH Function – In this video we show how the SWITCH function can be used as an alternative to the IFS or CHOOSE function.

DYNAMIC ARRAYS

This chapter covers Dynamic Arrays and has 10 lessons.

  • The Unique Function – In this video we show how the UNIQUE function extracts all unique entries across multiple columns.
  • The Unique Function and Tables – In this video we show that the UNIQUE function automatically updates with new data if the original data is in a table.
  • The SORT and SORTBY Functions – In this video we show how to use the SORT and SORTBY functions to sort arrays based on one or more columns.
  • The SORT and SORTBY Functions With Range Names and Tables – In this video we show how the SORT and SORTBY functions work with ranges and tables.
  • The FILTER Function – In this video we use the FILTER function to filter data based on single or multiple criteria.
  • The SEQUENCE Function – In this video we show how the SEQUENCE function can be used to insert a sequence of numbers with a constant increment in a range of cells.
  • The RANDARRAY Function – In this video we use the RANDARRAY function to generate a rectangular array of random numbers and also “shuffle” a deck of cards.
  • The #SPILL Operator – In this video we show  how using the # (Spill) operator, you can refer to the results of a  dynamic array formula in other formulas.
  • Dynamic Arrays and Nested Dropdowns – In this video we use Dynamic Arrays to create nested dropdown boxes. In the first dropdown you pick a college football conference. Then the 2nd dropdown just shows the teams in that conference.
  • Using Dynamic Arrays to Emulate a Pivot Table – In this video we show how to use Dynamic arrays to emulate a Pivot Table. When new sources of data are added, our calculations seamlessly update.

POWER PIVOT

This chapter covers Power Pivot and has 13 lessons.

  • Introduction to PowerPivot – In this video we show how to use PowerPivot to combine data sources and create a PivotTable.
  • Creating PivotTables From a Database – In this video we show how to use PowerPivot to import tables from an Access database and create a PivotTable based on the imported data.
  • DAX Functions – In this video we show how to use DAX functions to add new columns to a PowerPivot page and use the new columns in a PivotTable.
  • The DISTINCTCOUNT Function – In this video we use PowerPivot to create a Pivottable that compares a company’s actual sales to their forecasted sales.
  • Comparing Forecasted Sales to Actual Sales – In this video we use PowerPivot to create a PivotTtable that compares a company’s actual sales to their forecasted sales.
  • The RELATED DAX Function – In this video we show how the RELATED DAX function is used to pull data from one data source tab to another data source tab
  • Pivot Tables Based on PowerPivot Calculated Columns – In this video we show how to create PivotTables based on PowerPivot calculated columns.
  • Calculated Measures – In this video we use the CALCULATE function to create calculated measures.
  • Key Performance Indicators – In this video we use a KPI to visually summarize a calculated measure.
  • Using Power Pivot to Draw a Random Sample – In this video we show how to use Power Pivot to take a random sample from a sales database.
  • Using Power Pivot to Sample From a Subset of a Database – In this video we use Power Pivot to take a random sample from only 2019 sales invoices.
  • Median and Date Intelligence DAX Functions – In this video we show how to use the MEDIAN DAX function to compute median sales and use the DATESMTD DAX function to compute sales to date during the current month.
  • Date Intelligence Functions Part II – In this video we show how date intelligence functions are used to determine sales on a given day a year ago and sales to date during the same month a year ago.

FILLED AND 3D MAPS

This chapter covers Filled and 3D Maps and has 6 lessons.

  • World Filled Maps – In this video we create a filled map showing sales in different countries.
  • US Filled Maps – In this video we create a 3D map that shows sales from different sources in different counties and cities.
  • Building a 3D Energy Map – In this video we create a 3D map that shows the energy capacity from different sources in each state.
  • 3D Time Animation – In this video we create a movie that shows how sales in each state change over time.
  • Mapping the 2016 Election – In this video we use a Filled map to show how Trump won the 2016 election by winning the “Flyover States.”
  • Mapping NYC Subway Lines – In this video we show how 3D Maps can be used to show the location of NYC subway stations.

DATA TYPES

This chapter covers Data Types and has 5 lessons.

  • Introduction to Geography Data Types – In this video we show how Excel’s new geography data types let you create formulas that enter data about locations.
  • Geography Types and Excel Tables – In this video we show how Geography Data Types can be used with Excel Tables.
  • The FIELDVALUE Function – In this video we help you master the FIELDVALUE function which let’s you vary the property extracted for a location.
  • Resolving Ambiguity – In this video we show how to resolve ambiguity about the location of interest.
  • Stock Data Types – In this video you will learn how to use the new data types to extract data about a corporation.

POWER QUERY

This chapter covers Power Query and has 13 lessons.

  • Downloading Bitcoin Prices and Volumes – In this video we use Power Query to download Bitcoin price data and transform its appearance.
  • Downloading the Population of US Cities – In this video we use Power Query to download populations of the largest US cities and merge the city and state names in a single column.
  • Flattening a Table – In this video we use Power Query to flatten a table.
  • Turning a Flat File Into a Table – In this video we use Power Query to flatten a table.
  • Using a Formula to Add a Column – In this video we use an IF formula to compute a discounted price in a custom column.
  • Appending Queries – In this video we show how to append queries using Power Query.
  • Power Query Trick – In this video we show how you can combine all files in a folder and if you add new files the summary automatically updates when refreshed.
  • Filling Blanks in a Column – In this video we are given a list of NBA player statistics with most of the player’s teams missing. We use Power Query to fill in the missing team names and transpose the statistics from different columns to different rows.
  • Transforming Data Using Delimiters – In this video we split data at a comma and split the data into different rows. Then we split the data at a colon into two different columns.
  • Grouping Data with Power Query – In this video we show how to group sales data by region so that when new data is added, our calculations update to include calculations for new regions.
  • Using Power Query to Summarize Data Across Multiple Columns – In this video we show how to use Power Query to summarize sales by person and region. After refreshing, new data is automatically included in our summary.
  • Creating Custom Columns – In this video we create a custom column for revenue based on price and units sold and summarize revenue by year.
  • Pivoting Columns With Power Query – In this video our spreadsheet contains a single piece of data for a single player in each row. Each player has 5 rows of data and we show how to pivot the data so each player’s data appears in a single row and 5 columns.

MACROS

This chapter covers Macros and consists of 10 lessons.
 
  • Inserting the Developer Tab – In this video we show how to make the Developer Tab appear on your ribbon.
  • Inserting a List of Names – 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.
  • Relative References With Macros – In this video we show how to use Relative References to make sure our list of names can be entered anywhere in our workbook.
  • Using Macros to Format Cells – 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).
  • Formatting a Range of Variable Size – In this video we show how to create a macro that formats a range of variable size.
  • Using a Macro to Freeze Values – In this video we create a macro that coverts formulas in a selected range to numerical values.
  • Using Macros For Reporting – 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.
  • Creating a Border With a Macro – In this video we show how to create a macro that places a border around a range of selected cells.
  • Personal Macro –In this video we discuss how to save a macro to your Personal Macro Workbook.
  • Sorting Macro –In this video we show how to create a macro that will sort a list by last and first name.

RISK MODELING

This course covers Monte Carlo Simulation with the Palisade @RISK Add-In and consists of 50 topics.
 
  • Introduction to @Risk – In this video we use the classic news-person problem with discrete demand to introduce Monte Carlo Simulation with @RISK.
  • Simulations With The Normal Random Variable – In this video we solve the classic news-person problem with normal demand to introduce Monte Carlo Simulation with @RISK. We also illustrate the different graphs that can be used to summarize an @RISK simulation.
  • Using Riskstatistical Functions – In this video we show how to use the RISKMEAN and RISKSTDDEV functions to update the mean and standard deviations of a simulation as the simulation is run.
  • Introduction To Capital Budgeting And The Triangular Random Variable – In this video we use @RISK and the triangular random variable to determine if GM should produce a new car.
  • Modeling The Product Life Cycle And Sensitivity Analysis With @Risk – In this video we show how to model sales volume over time using the product life cycle and explain how @RISK can be used to determine the input random variables that have the most influence on simulation output cells.
  • RiskGeneral Random Variable – In this video we show how the RISKGENERAL random variable can be used to model a continuous random variable.
  • The RiskCumul Random Variable – In this video we show how the RISKCUMUL function can be used to model a continuous random variable.
  • The RISKTRIGEN Random Variable – In this video we show how the RISKTRIGEN function is used to generalize the triangular random variable.
  • Statistical Analysis Of @Risk Outputs – In this video we show how to find a 95% confidence interval for the average value of a simulation output cell and a range that includes 95% of the actual values for an @RISK output cell.
  • Should We Drill For Oil? – In this video we ask the question, “How can we use @RISK to determine whether it is a good idea to drill for oil?”
  • The Scenario Approach To Capital Budgeting – In this video we show how uncertain market share and development expenses can be modeled by defining scenarios for product acceptance.
  • Plugin Means For Option Pricing – In this video we use a simple option pricing example to show that replacing a random future stock price by its mean does not yield the correct mean for the output cell (cash flow from the option).
  • Valuing An Acquisition – In this video we show how to value an acquisition, including the concept of Terminal Value.
  • Using @Risk’S Goal Seek Capability – In this video we use @RISK’s Goal Seek Capability to determine the terminal growth rate that makes an acquisition a good deal.
  • Simulating The NBA Finals- In this video we show how to determine the chance that a team wins a best of 7 NBA series. We also determine the probability that the series will go 4, 5, 6, or 7 games.
  • Simulating Craps – In this video we show how to determine the chance of winning at craps.
  • Three Dimensional Random Walk – In this video we analyze a three dimensional random walk and show that like Charley on the MTA (Kingston Trio) you may never return!
  • Birthday Problem – In this video we show that with 23 people in the room there is a 50-50 chance that at least two people have the same birthday.
  • Finding Your Best Partner – In this video we show how to determine how many people to interview in order to maximize your chance of finding your best partner.
  • Calculating Poker Probabilities – In this video we use @RISK to determine the chance of getting one pair, two pair, or a full house in 5 card draw poker.
  • The Optimal Bid – In this video we show how @RISK can be used to determine the optimal bid on a construction project.
  • Introduction To RISKOPTIMIZER- In this video we show how RISKOPTIMIZER allows you to generalize a RISKSIMTABLE and solve for the order quantity of calendars that maximizes expected profit in the presence of uncertain demand.
  • Multi-Product Newsperson Problem – In this video we use RISKOPTIMIZER to solve a multi-product newsperson problem.
  • Scheduling Hospital Nurses – In this video we find the minimum number of nurses needed to ensure that during during a week there is at most a 5% chance of not having enough nurses.
  • Bid Optimization With RISKOPTIMIZER – In this video we show how to use RISKOPTIMIZER to determine a bid that maximizes expected profit in the presence of uncertainty.
  • Rescuing The Iran Hostages – In this video we show how @RISK could have increased the chances of a successful rescue of the hostages during the 1980 Iran Hostage Crisis.
  • Sequencing Jobs With Uncertain Duration – In this video we use RISKOPTIMIZER to sequence jobs of uncertain duration to minimize the expected number of late days.
  • Choosing A Portfolio Of Capital Budgeting Projects – In this video we use RISKOPTIMIZER to choose a subset of capital budgeting projects that meet a spending constraint.
  • Modeling Correlated Random Variables – In this video we show how to use the RISKCORRMAT function to model correlated (non-independent) random variables. You will also learn how to overlay multiple simulation outputs on a single graph.
  • Creating Multiple Instances of Correlations – In this video we show how you can create multiple instances of correlated random variables. For example, the percentage change in the price of each of 4 stocks on day N may be correlated, but none of the day N percentages are correlated with any other day.
  • Generating Future Investment Scenarios – In this video we use re-sampling to generate future scenarios for returns on important asset classes.
  • Optimal Portfolios Using Var And Sharpe Ratio – In this video we show how to find portfolios that optimize Value at Risk (VAR) and the Sharpe Ratio.
  • Optimal Portfolios Using Stress Testing And Downside Risk – In this video we show how to find portfolios that maximize a portfolio’s worst case or minimize a portfolio’s downside risk.
  • Optimal Portfolios Controlling A Portfolio’S Beta Or Conditional Var – In this video we show how to find portfolios that control risk by setting the portfolio’s Beta or maximizing the portfolio’s Conditional VAR.
  • Fitting A Discrete Distribution To Data – In this video we use @RISK’s Distribution Fitting Feature to show that goals scored in an NHL game follow a Poisson random variable.
  • Fitting A Continuous Random Variable To Data – In this video we use @RISK’s Distribution Fitting Feature to show that the margin of victory in an NFL game follows a normal random variable.
  • The Winner’s Curse – In this video we show how to incorporate the Winner’s Curse into your optimal bidding strategy.
  • The Black-Scholes Option Pricing Formulas – In this video we show how to use the Black-Scholes formula to price European call and put options.
  • Using The Lognormal Random Variable To Simulate Stock Prices – In this video we show how to use financial data from Yahoo.com and the Lognormal random variable to model future stock prices.
  • Butterfly or Straddle? – In this video we discuss the relative merits of a Butterfly or Straddle strategy involving options of Microsoft stock.
  • Introduction To Utility Theory – In this video we show how utility functions can be used to make decisions under uncertainty which incorporate the decision-maker’s attitude towards risk.
  • Exponential Utility – In this video we describe how to use the exponential utility function to model a decision-makers attitude towards risk.
  • Asset Allocation Via Expected Utility – In this video we show how to use utility theory to tailor an asset allocation to a decision-makers attitude towards risk.
  • Project Management With @Risk – In this video we show how to use @RISK to model the uncertain duration of a project.
  • Finding The Probability That Activities Are Critical – In this video we use @RISK to estimate the probability that each of a project’s activities are critical.
  • Introduction To Re-Sampling – In this video we show how re-sampling can be used to replace many hard to remember statistical hypothesis tests.
  • Is a New Cancer Drug Worthwhile? – In this video we use re-sampling and results from 12 patients to show a new cancer drug is a significant improvement over an old cancer drug.
  • Was The 1970 Draft Lottery Fair? – In this video we use re-sampling to show that the 1970 draft lottery was not random.
  • Beta Re-Sampling – In this video we use re-sampling to show that there is an 80% chance that Dell has the highest beta among a list of 6 stocks.
  • A Bidding Paradox – In this video we use @RISK to resolve a bidding paradox.
  • Heads Or Tails? – In this video we show that when tossing a coin repeatedly it is much more likely that THH comes before HHH.
  • Should We Have Known Madoff Was A Fraud? – In this video we use the Sharpe Ratio and re-sampling to to show that the SEC should have realized well before 2008 that Madoff’s fund was a fraud.
  • How to Hedge FX Risk?- In this video we use @RISK to show how a company can hedge FX risk.
  • Fashion Ordering Part One – In this video we use RISKOPTIMIZER to determine how a fashion retailer can maximize expected profit when early sales of a product are a good indicator of future demand.
  • Fashion Ordering Part Two – In this video we continue our discussion on fashion ordering.
  • Introduction To Supply Contracts – In this video we introduce a simple supply chain where coats are manufactured in Asia for a US retailer.
  • Buyback Contracts – In this video we show that when the manufacturer is willing to buy back leftover coats both the retailer and manufacturer benefit.
  • Revenue Sharing Contracts – In this video we show that if the retailer shares some revenue with the manufacturer and the manufacturer reduces the wholesale price, then the performance of the supply chain improves.
  • Optimizing The Whole Supply Chain – In this video we show how buy back and revenue sharing contracts drive the supply chain to maximize expected profit.
  • Pricing Path dependent options by Simulation – In this video we show how to use simulation to price path dependent options.
  • Risk Neutral Valuation – In this video we show how simulation and the risk neutral approach to asset valuation allow us to use simulation to price a European call and put option.

PRELIMINARIES

  • Introduction To Descriptive Statistics – In this video we introduce descriptive statistics.
  • Investment Returns – In this video we analyze annual returns on US investments.
  • Random Variables and Women’s Heights – In this video we define random variables and analyze the height of American women.

WAS THE 1969 DRAFT LOTTERY FAIR?

 
  • 1969 Draft Lottery Part One – This video shows discusses the 1969 Vietnam Draft Lottery.
  • 1969 Draft Lottery Part Two – In this video we use a two sample test to show the 1969 draft lottery was not fair and the 1970 lottery was fair.
  • Draft Lottery Boxplots – In this video we use Boxplots to show the 1969 lottery was unfair and 1970 lottery was fair.

WHO WON: GORE OR BUSH?

 
  • Who Won: Gore or Bush? – In this video we analyze the controversial 2000 POTUS Election.

WAS LIVERPOOL OVER BARCELONA THE GREATEST UPSET IN SPORTS HISTORY?

 
  • Ponzi Schemes – In this video we use betting predictions to show Leicester winning the Premiere League was the greatest upset of all time.

HOW DID BERNIE MADOFF KEEP HIS FUND GOING?

 
  • Ponzi Schemes – This video shows how Ponzi schemes work.
  • Bernie’s Alleged Strategy – This video explains Madoff’s “split strike strategy”.
  • Benford’s Law and Country Populations – This video shows that 30% of all country populations begin with 1.
  • Why Benford’s Law Works – This video hows how to use a simulation to show why Benford’s Law works.
  • Benford’s Law and Madoff Returns – This video shows that Madoff’s made up returns violate Benford’s Law.
  • Sharpe Ratio for Madoff – This video shows that Madoff’s Sharpe Ratio was suspiciously high.

IS THE LOT OF THE US WORKER IMPROVING?

 
  • The US Worker – This video shows the lot of the typical US worker has not improved much over the last fifty years.

MEASURING INCOME INEQUALITY 

 
  • Measures of Income Inequality – In this video we discuss the Gini, Palam, and Atkinson indices of inequality.

MODELING RELATIONSHIPS BETWEEN TWO VARIABLES 

 
  • Estimating a Demand Line and the Beta of a Stock – In this video we discuss determining the least square demand curve and estimating the Beta of Madoff’s made up returns.
  • RSQ, Correlations, and Regression to the Mean – In this video we discuss why a high RSQ may not prove anything and define and discuss correlation.
  • The Power Curve and Elasticity – In this video we discuss the power curve and its relationship to price elasticity

INTERGENERATIONAL MOBILITY 

 
  • Introduction to Intergenerational Mobility – In this video we discuss how economists measure income mobility between generations.
  • Computing Rank Rank and IGE – In this video we show how to compute IGE and Rank-Rank measures of Intergenerational mobility.
  • Quintiles and the Gatsby Curve – In this video we discuss how quintiles relate to Intergenerational mobility and look at Krueger’s Gatsby Curve that relates income inequality and intergenerarional mobility.

IS ANDERSON ELEMENTARY A GOOD SCHOOL?

 
  • Free Lunch Percentage – In this video we show that the fraction of students getting free lunches explains around 50% of the variation in pass rate on standardized tests.

TEACHER EVALUATION METHODS 

 
  • Teacher Evaluation Based on Tests – In this video we discuss the pros and cons of evaluating K-12 teachers based on test scores.
  • Common Teacher Evaluation Methods – In this video we use Pivot Tables to illustrate simple methods for evaluating teachers based on test scores.

BERKELEY, BUSES, CARS AND PLANES 

 
  • Simpson’s Paradox – This video shows women are less likely to be accepted in their major than men, but in each major women are more likely to be accepted. How can this happen?
  • Three More Paradoxes – In this video we resolve three more analytic paradoxes.

MANU OR MELO FOR HOF?

 
  • Is Carmelo Anthony a Hall of Famer? – In this video we show that Manu Ginobili is more deserving than Carmelo Anthony of election to the NBA Hall of Fame.

WAS DEREK JETER A GREAT FIELDER?

 
  • Was Derek Jeter a Great Fielder? – In this video we show that although he won 5 gold gloves, Derek Jeter was a below average fielder.

DRIVE FOR SHOW PUTT FOR DOUGH?

  • Golf Analytics – In this video we introduce you to the concept of strokes gained, which has revolutionized golf.

WHAT’S WRONG WITH THE NFL QB RATING?

 
  • Using Regression To Calculate NFL QB Rating – In this video we show how to use multiple regression to reverse engineer the NFL QB rating formula.
  • Interpreting Multiple Regression Output  – In this video we show how to interpret multiple regression output.
  • Using The TREND Function To Make Predictions – In this video we we use the TREND function to make multiple regression predictions.
  • Problems With NFL QB Rating – In this video we show that the NFL QB rating has fatal flaws.
  • Alternatives To The NFL QB Rating – In this video we discuss alternatives to the NFL QB rating.

SOME SPORTS HAVE ALL THE LUCK?

 
  • Which Sports Have The Most Skill and Least Luck? – In this video we develop a simple method for measuring the relative importance of skill and luck in team sports.

GERRYMANDERING

 
  • Gerrymandering – In this video we discuss how state legislatures can draw House districts to make their congressional delegation non-representative of their state’s population.

EVIDENCE BASED MEDICINE

 
  • Scurvy and Evidence Based Medicine – In this video we discuss randomized controlled trials and evidence based medicine.
  • The Streptomycin Trial – In this video we discuss the RCT that showed streptomycin is effective in treating TB.
  • RCT’s and Hormone Replacement – In this video we discuss the complex issue of whether hormone replacement increases mortality.

COMPARING HOSPITALS

 
  • Comparing Hospitals – In this video we discuss some of the pitfalls involved in rating hospitals.

WHAT IS THE WORST HEALTH CARE PROBLEM IN YOUR?

 
  • Using DALY’s to Compare Health Problems – In this video we show how the DALY (Disability Adjusted Life Years) can be used to determine the importance of difference health problems in any country.
  • DALY Dashboards – In this video we explore IHME’s DALY dashboards to show how different regions of the world face different health problems.

DOES A MUTUAL FUND’S PAST PERFORMANCE PREDICT FUTURE PERFORMANCE?

 
  • Introduction to Mutual Fund Ratings – In this video we discuss Morningstar’s method for rating mutual funds.
  • The Sortino Ratio – In this we show how the Sortino Ratio improves on the Sharpe Ratio.
  • Seeking Alpha – In this video we discuss alpha, which is an important measure of investment performance.
  • Do Morningstar Ratings Do A Good Job Of Predicting Future Performance? – In this video we discuss a Wall Street Journal study that shows 5 star and 1 star funds regress to the mean in future performance.
  • How Does The Performance Fee Affect Long-Run Investment Performance? – In this video we show that if performance is identical, an advisor taking a 2% annual fee reduces your 30 year ending capital by 30% compared to an advisor taking a 0.1% annual fee.

IS VEGAS GOOD AT PICKING NFL GAMES?

 
  • Why Do NFL Bets Work? – In this video we describe the NFL point spread, totals, and money line bets.
  • Evaluating Forecast – In this video we discuss the key measures of forecast bias and accuracy.
  • Are Point Spreads Biased? – In this video we show that NFL point spreads exhibit little or no bias.

WILL MY NEW HIRES BE GOOD EMPLOYEES?

 
  • Intelligence Is Key Predictor Of Job Performance – In this video we discuss research that indicates that intelligence is the best predictor of job performance.

SHOULD I GO TO PRINCETON OR STATE U?

 
  • Ivy League Is Overrated – In this video we show that after adjusting for student quality, researchers found Ivy League Schools do not add more value than quality state schools.

WILL MY FAVORITE SPORTS TEAM BE GREAT NEXT YEAR?

 
  • Regression To The Mean In The NFL And NBA – In this video we show how regression to the mean explains why great teams fall to earth the next season.

WHAT’S WRONG WITH THE NFL QB RATING?

 
  • Using The Inverted Yield Curve And Sahm Rule To Predict Recessions – In this video we show how the Inverted Yield Curve and the Sahm Unemployment rate rule can be used to predict recessions.
  • Conditional Formatting With A Formula – In this video we show how formulas can be used to create conditional formats.
  • Introduction To SPC – In this video we explain the basics of control charts and how to tell if a process mean has shifted.
  • Using Control Charts To Predict The 2008 Housing Crisis – In this video we use control charts to identify the housing price bubble and housing price collapse.

WHAT’S WRONG WITH THE NFL QB RATING?

 
  • What Data Is Used By Target To Predict If A Woman Is Pregnant? – In this video we discuss the data used to predict if a woman is pregnant and the technique of logistic regression.
  • More On Logistic Regression And Target – In this video we show that for any predicted likelihood of a woman being pregnant, the logistic regression model does not exhibit much bias.

HOW DOES NETFLIX RATE MOVIES?

 
  • Introduction To Collaborative Filtering – In this video we introduce the concept of collaborative filtering and give an example of user based collaborative filtering.
  • User Based Collaborative Filtering, The Netflix Prize And House Of Cards – In this video we give an example of User Based Collaborative filtering and discuss the Netflix Prize competition and the reason why Netflix made House of Cards.

CAN THE APPLE WATCH SPOT THE AFIB?

 
  • What Are Sensitivity, Specificity And Posterior Probability? – In this video we define the sensitivity and specificity of a medical test.
  • Receiver Operator Characteristic Curve (ROC)– In this video we discuss the construction and interpretation of the ROC curve.
  • Apple Heart Study– In this video we show that the Apple Heart Study has low sensitivity.

IS PROACTIVE POLICING EFFECTIVE?

 
  • Does Hot Spot Policing Reduce Crime? – In this video we discuss a study that uses an RCT to show adding police reduces crime in high crime areas.
  • Other Police Strategies – In this video we look at predictive policing, stop and frisk, CCTV, and broken windows approaches to policing.

GUESS HOW MANY ARE COMING TO DINNER?

 
  • A Simple Model To Predict Daily Customers – In this video we develop a model using trend, day of week, week of year, and holidays to predict customer count at a restaurant.
  • Setting Up The Spreadsheet – In this video we explain the formulas needed to create and evaluate our daily forecasts.
  • Using The Excel Solver – In this video we introduce the Excel Solver and use it to find the best customer forecasts.
  • Which Variables Are Most Useful For Forecasting Customer Counts? – In this video we show the day of the week and holidays are the most useful variables in creating our forecasts.

PREDICTION MARKETS

 
  • How Can Prediction Markets Be Used To Predict The Future? – In this video we show how prediction markets provide a simple mechanism for making accurate future forecasts.

THE ABCs OF POLLING

 
  • How Do Polls Work? – In this video we discuss important concepts needed to understand polling and look at reasons why polls sometimes fail.

WHY DID THE DRESS GO VIRAL?

 
  • Why Did The Buzzfeed Dress Go Viral? – In this video we discuss memes, social lift ,and Instagram engagement.

PREDICTING GAME OF THRONES RATINGS

 
  • Taylor Swift Vs. Adele – In this video we compare search activity for these two great performers.
  • Using Google Trends  – In this video we show how Google Trends can be used to predict ratings for each GOT premiere episode.

DOES SMOKING CAUSE LUNG CANCER?

 
  • Smoking And Lung Cancer – In this video we review the history of the debate about whether smoking cases lung cancer.

WHY ARE THE HOUSTON ROCKETS A GOOD BASKETBALL TEAM?

 
  • Middle School Math And The NBA – In this video we show how the Rockets’ shot selection, not their shooting ability contributes to their great success.

WHY ARE BUNTS AND INTENTIONAL WALKS DECREASING?

 
  • Why Are Bunts And IBB’s Less Common? – In this video we explore the reasons why in most situations bunts and intentional walks are not beneficial.

NFL DECISION MAKING

 
  • NFL Decision Making Is Flawed – In this video we show NFL teams should pass more, go for it more on 4th down, and go for 2 more often.

CHOLERA IN LONDON

  • John Snow The G.O.A.T Of Doctors – In this video we show you how the great John Snow showed contaminated water caused cholera.
  • Bubble Charts In The Time Of Cholera – In this video we show how to create a bubble chart that shows the preponderance of cholera deaths near the Broad Street pump.

HOW DOES THE MARKETING MIX AFFECT SALES?

 
  • Predicting Painter’s Tape Sales – In this video we develop a model to predict product sales based on quarter of the year, product on display, availability of coupons, and product price. 
  • Using The GRG Multistart Solver Engine – In this video we show how to use the GRG multistart engine to fit complex forecasting models.

EXPLAINING THE PARETO PRINCIPLE

 
  • The Pareto Principle And Power Laws – In this video we show how power laws lead to the 80-20 rule.
  • The Long Tail And Power Law For Income – In this video we explain how 10% of all book titles can generate 84% of all sales and how power laws explain the Pareto distribution of income.
  • Rich Get Richer And Websites – In this video we show how the rich get richer theory explains how few websites dominate the Internet.

DOES WHERE I GROW UP MATTER?

 
  • Cook County Vs. Dupage County – In this video we explore how moving a low income child to a better neighborhood can give them a better chance at success.

THE WAITING IS THE HARDEST PART

 
  • Analyzing Waiting Lines – In this video we show how to determine the average time and number of people in a queueing system.
  • Data Tables And Queuing – In this video we use data tables to perform a sensitivity analysis on a queueing system.

DO ROUNDABOUTS WORK?

 
  • Why Do Roundabouts Work? – In this video we discuss the pros and cons of roundabouts.

RED LIGHT, GREEN LIGHT, NO LIGHT?

 
  • How Do Ramp Meters Work? – In this video we discuss how ramp meters improve freeway traffic performance.

HOW TO IMPROVE K-12 EDUCATION

 
  • Reducing K-12 Class Size – In this video we discuss how reducing K-2 class size improves test scores.
  • Cost Benefit Analysis – In this video we estimate a 9.8% annual rate of return on reduced K-12 class size.

HOW DOES A/B TESTING WORK?

 
  • A/B Testing – In this video we describe A/B testing, which is used to improve performance of websites and Facebook Ads.

HOW SHOULD I ALLOCATE MY RETIREMENT PORTFOLIO?

 
  • Introduction To Portfolio Optimization – In this video we discuss how to trade off risk vs. return when determining your portfolio’s asset allocation.
  • Solver Portfolio Optimization – In this video we work through an asset allocation with the Excel Solver.

HOW DO HEDGE FUNDS WORK?

 
  • Introduction To Hedge Funds – In this video we discuss long, short, and market neutral hedge funds.
  • Convertible Bond And Merger Arbitrage – In this video we discuss convertible bond and merger arbitrage strategies.
  • Global Macro And Worst Case Strategies – In this video we discuss Global Macro strategies and a portfolio optimization approach that maximizes the worst case return.

INVENTORY

 
  • How Much Should We Order? – In this video we discuss the economic order quantity (EOQ) that tells you the cost minimizing order quantity.
  • When Should We Order? – In this video we discuss how to use the service level approach to determine how low to let inventory go before you reorder.

HOW DOES THE UPS DRIVER KNOW THE ORDER TO DELIVER PACKAGES?

 
  • The Traveling Salesperson Problem (TSP) – In this video we discuss the problem of visiting a list of cities once and returning home and traveling the least distance.
  • Using The Solver To Solve The TSP – In this video we use the “All Different” feature of the Solver to solve a TSP.

ANALYTICS IN PRESIDENTIAL ELECTIONS

 
  • Obama And Trump’S Use Of Analytics – In this video we discuss how analytics was a key factor in the 2012 and 2016 Presidential elections.
  • Solver And Logistic Regression – In this video we show how to use the Solver and logistic regression to estimate the probability a voter is a Democrat.

CAN ANALYTICS SAVE THE REPUBLIC?

 
  • How To Pick A Winner – In this video we show it is really hard to find a rule that is good for choosing the winner in an election.
  • Approval Voting And Quadratic Voting – In this video we describe Approval and Qaudratic voting.

THE WINNER’S CURSE

 
  • The Winner’s Curse – In this video we discuss why people overpay when bidding for things.
  • Avoiding The Winner’s Curse – In this video we use simulation to show how to adjust a bid to avoid the winner’s curse.

MUSIC AND ANALYTICS

 
  • Can Analytics Recognize, Predict, Or Write A Hit Song? – In this video we discuss how Shazam works, how Norah Jones’ debut album was sure to be a smash hit, and how computers try and write songs.

CAN ALGORITHMS IMPROVE PAROLE DECISIONS?

 
  • How Are Algorithms Used In Parole Decisions? – In this video we discuss the problem of disparate impact when algorithms are used in parole decisions.
  • ProPublica And Parole Bias – In this video we give an introduction to AI and show how it can improve parole decisions.
  • AI And Parole Decisions – In this video we introduce you to the concept of strokes gained, which has revolutionized golfP

DOES THE BASEBALL SHIFT WORK?

 
  • The Shift – In this video we show how the baseball shift kills left-hand hitters.

HOW DID ANALYTICS HELP THE DALLAS MAVERICKS WIN THE 2011 NBA TITLE?

 
  • Rating Players And Lineups – In this video we show how analytics gave the Dallas Mavericks and New York Knicks an edge in the NBA.

WHO GETS THE HOUSE IN THE HAMPTONS?

 
  • How Can We Fairly Resolve Conflict Among Two Parties? – In this video we show how the Excel Solver can come up with an optimal division of property in a divorce.