Array Formulas Syllabus

Array Formulas

This course covers Array Formulas and consists of 17 topics.

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
Learn More