Excel Statistical Analysis - Comprehensive Worksheet Formula & Model Building

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Dive into a comprehensive 43-minute video tutorial on Excel statistical analysis, focusing on worksheet formulas, functions, and model building. Master the fundamentals of Excel formula elements, order of operations, and guidelines for constructing effective worksheet models. Explore number formatting techniques, including the use of the ROUND function, and understand Excel's Golden Rule. Learn to build and document Excel worksheet formula models using functions like ISNA and FORMULATEXT. Discover various formula types, including number, text, logical, aggregate, and single input-output formulas. Compare old school methods of relative and absolute cell references with new Dynamic Spilled Array Formulas available in Microsoft 365 Excel. Gain insights into Excel Table Column Formulas and their unique properties. Practice creating percentage of total and percentage change formulas using both traditional and modern techniques. Download the accompanying Excel file to follow along and reinforce your learning.

Syllabus

) Introduction.
) Topics in video.
) Worksheet Formula Elements: what can go into a formula.
) Excel Formula Order of Operations.
) Guidelines for building Excel Worksheet Formula Models.
) Number Formatting as a Façade and how it can lead to misleading models if it is not understood.
) The power of General Number Formatting as an eraser, including keyboard shortcut.
) When you must use the ROUND function rather than Number Formatting.
) Excel’s Golden Rule.
) Building an Excel Worksheet Formula Model.
) ISNA and FORMULATEXT functions to document model..
) Summary of Formula Types.
) Example of Number, Text and Logical Formulas.
) Example of Aggregate Formula and Single Input-Output Formula. Using AVERAGE, COUNT, COUNTA and ROWS functions..
) Examples of Dynamic Spilled Array Formulas (only available in Microsoft 365 Excel). Compare and contrast this new school way of making formulas with the old school way..
) Example of Relative and Absolute Cell References. This is the Old School way to make formulas.
) Dynamic Spilled Array Formula. This new school method is MUCH easier than the older methods.
) Three reasons Dynamic Spilled Array Formula are more efficient than the old methods.
) Second example of Dynamic Spilled Array Formula, including Spilled Range Operator #.
) Example of Scalar Array Formula.
) How Excel Table Column Formulas are different than Single Input-Output Formulas and Dynamic Spilled Array Formulas..
) % of Total and % Change Formulas. Dynamic Spilled Array Formula and Old School Methods..
) Video Summary.
) Conclusion and Video Links.