Offset And Indirect Functions Syllabus

Offset and Indirect Functions

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

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 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 coefficent (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 coefficent (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.
Learn More