Dive into a comprehensive Excel tutorial that explores various methods for performing conditional calculations using formulas. Master the use of built-in functions like SUMIFS, AND, OR, and IS functions, as well as array formulas and D functions. Learn to create cross-tabulated tables, generate monthly sales reports, and develop frequency tables using COUNTIFS. Explore the application of defined names and scope in Excel workbooks, and understand how to use OR criteria for adding and counting with SUMIFS and SUMPRODUCT. Discover techniques for credit analysis in accounts receivable and bank checkbook management. Compare the effectiveness of PivotTables versus formulas for conditional calculations. Download accompanying files to practice along with the instructor throughout this in-depth, 1-hour 23-minute lesson on advanced Excel formula techniques.
Overview
Syllabus
) Introduction.
) AND Logical Test (AND Criteria).
) AND Function.
) SUMIFS, COUNTIFS, AVERAGEIFS functions with AND Criteria.
) Create Names From Selection.
) Cross Tabulated Table for adding with 3 criteria: SUMIFS and Mixed Cell References..
) Between Logical Tests, Create Monthly Sales Report with SUMIFS..
) Create Monthly Sales Report with SUMIFS and EOMONTH functions..
) Count Sales Between Upper and Lower Limit (Frequency Table) with COUNTIFS (Alos consider how this method is different from FREQUENCY Array Function)..
) D Functions (Database functions) with AND Criteria.
) Array Formulas with AND Criteria. Use Two IF Functions. See how to calculate Standard Deviation with two conditions or criteria..
) OR Logical Test (OR Criteria).
) Defined Names and Scope in an Excel Workbook.
) OR Function.
) OR Criteria looking at one column using SUMIFS and SUMPRODUCT for adding and COUNTIFS and SUMPRODUCT for counting. Function Argument Array Operations..
) Average calculation with OR Criteria: AVERAGE & Array Formula (for 1 or more columns).
) D Functions (Database functions) with OR Criteria.
) AND and OR and IF Functions for Credit Analysis for Accounts Receivable Department.
) IF Function & IS functions. Bank Checkbook Example..
) Conditional Calculations: PivotTable or Formulas?.
) Summary.
Taught by
ExcelIsFun
