Auditing Tools, Circular References, and IF Functions
This course covers Auditing Tools, Circular References, and IF Functions and consists of 23 topics.
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 – In this video we show how to use the IF and ISERROR functions to adjust a professor’s number of students based on whether a course lasted half a semester.
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 Statments 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