Excel Statistical Analysis 03: Comprehensive Worksheet Formula & Model Building Video
Download Excel File: https://excelisfun.net/files/Ch01-ESA.xlsm
Learn the fundamentals of Excel worksheet formulas, functions and how to build worksheet models.
Topics:
(00:00) Introduction
(00:19) Topics in video
(00:45) Worksheet Formula Elements: what can go into a formula
(01:57) Excel Formula Order of Operations
(03:23) Guidelines for building Excel Worksheet Formula Models
(03:57) Number Formatting as a Façade and how it can lead to misleading models if it is not understood
(05:48) The power of General Number Formatting as an eraser, including keyboard shortcut
(07:00) When you must use the ROUND function rather than Number Formatting
(11:49) Excel’s Golden Rule
(12:20) Building an Excel Worksheet Formula Model
(15:15) ISNA and FORMULATEXT functions to document model.
(19:10) Summary of Formula Types
(21:23) Example of Number, Text and Logical Formulas
(24:50) Example of Aggregate Formula and Single Input-Output Formula. Using AVERAGE, COUNT, COUNTA and ROWS functions.
(28:26) 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.
(29:34) Example of Relative and Absolute Cell References. This is the Old School way to make formulas
(31:30) Dynamic Spilled Array Formula. This new school method is MUCH easier than the older methods
(32:37) Three reasons Dynamic Spilled Array Formula are more efficient than the old methods
(34:37) Second example of Dynamic Spilled Array Formula, including Spilled Range Operator #
(35:07) Example of Scalar Array Formula
(36:38) How Excel Table Column Formulas are different than Single Input-Output Formulas and Dynamic Spilled Array Formulas.
(40:00) % of Total and % Change Formulas. Dynamic Spilled Array Formula and Old School Methods.
(42:28) Video Summary
(43:00) Conclusion and Video Links
Видео Excel Statistical Analysis 03: Comprehensive Worksheet Formula & Model Building Video канала excelisfun
Learn the fundamentals of Excel worksheet formulas, functions and how to build worksheet models.
Topics:
(00:00) Introduction
(00:19) Topics in video
(00:45) Worksheet Formula Elements: what can go into a formula
(01:57) Excel Formula Order of Operations
(03:23) Guidelines for building Excel Worksheet Formula Models
(03:57) Number Formatting as a Façade and how it can lead to misleading models if it is not understood
(05:48) The power of General Number Formatting as an eraser, including keyboard shortcut
(07:00) When you must use the ROUND function rather than Number Formatting
(11:49) Excel’s Golden Rule
(12:20) Building an Excel Worksheet Formula Model
(15:15) ISNA and FORMULATEXT functions to document model.
(19:10) Summary of Formula Types
(21:23) Example of Number, Text and Logical Formulas
(24:50) Example of Aggregate Formula and Single Input-Output Formula. Using AVERAGE, COUNT, COUNTA and ROWS functions.
(28:26) 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.
(29:34) Example of Relative and Absolute Cell References. This is the Old School way to make formulas
(31:30) Dynamic Spilled Array Formula. This new school method is MUCH easier than the older methods
(32:37) Three reasons Dynamic Spilled Array Formula are more efficient than the old methods
(34:37) Second example of Dynamic Spilled Array Formula, including Spilled Range Operator #
(35:07) Example of Scalar Array Formula
(36:38) How Excel Table Column Formulas are different than Single Input-Output Formulas and Dynamic Spilled Array Formulas.
(40:00) % of Total and % Change Formulas. Dynamic Spilled Array Formula and Old School Methods.
(42:28) Video Summary
(43:00) Conclusion and Video Links
Видео Excel Statistical Analysis 03: Comprehensive Worksheet Formula & Model Building Video канала excelisfun
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
YTL Excel #130: Income Statement from Excel Table: Array ForExcel Finance Class 74: IRR and Non-conventional Cash Flows, Plot Chart To See Multiple IRRCtrl + Shift + Enter: Excel Array Formulas #05: Function Argument Array OperationsFree Data Analysis Class: Intro to PivotTable, Power Query, Power Pivot, Power BI & Big DataIntroduction to the excelisfun Channel @ YouTube: Excel & Power BI Videos & Free Classes_Store Full Table to One Column? Excel Power Query Puts Records in One Cell! Excel Magic Trick1734.Power Query Running Count with Grouping & Add Index - Excel Magic Trick 1589Power Query to Import Excel File From OneDrive SharePoint into PivotTable, Refresh New Data EMT 1756Excel 2013 Statistical Analysis #5 Data Categorical, Quantitative, Nominal, Ordinal, Interval, RatioMSPTDA #02: Data Analysis Business Intelligence Terms: Microsoft Power Tools for Data Analysis Classexcelisfun’s new book The Only App That Matters Book is Released!!! Microsoft 365 Excel = The Best!MSPTDA 12: Using Locale in Power Query Power BI: Import & Append Text Files from Different CountriesSpilled Array Formulas Make Excel Life Easy. Compare Two Lists Extract Items Excel Magic Trick 1750MSPTDA 14: Power Pivot Intro #2: Amazing Columnar Database Importing Millions of Rows Data to ExcelMSPTDA 21: Power Query: Reduce Data Model Size, Transformations to Columnar Database SizeOffice 2013 Class #37: Excel Basics 19: SUMIFS, COUNTIFS: Add & Count with Criteria/ConditionsHighline Excel 2013 Class Video 05: Cell References: Relative, Absolute & Mixed (8 Examples)INDEX & MATCH or XLOOKUP for Lookup Formula in SUMIFS Criteria Argument. Excel Magic Trick 1668.E-DAB 09: Power BI Desktop: Data Modeling & Interactive VisualizationsExcel Two Way Lookup with VLOOKUP & MATCH Functions - Excel Magic Trick 1567MSPTDA 08.5: Power Query Group By Unique List or Consecutive Occurrences