Oct 5, 2022 6:00 AM

Logical Tests: Excel Formulas, Conditional Formatting, PivotTables, Power Query, & More- 365 MECS 07

External YouTube Channel

Logical Tests: Excel Formulas, Conditional Formatting, PivotTables, Power Query, & More

Download Excel file

Topics in video:

  • Introduction
  • What is a Logical Test in Excel?
  • Examples of six most common types of logical tests with Filter Feature and PivotTable
  • Examples of Single Condition, Contains and NOT Logical Tests
  • Description of AND Logical Test and OR Logical Test
  • Description of a Complex Logical Test
  • Comparative Operators
  • Comparative Operators Examples
  • IS functions
  • Boolean Values
  • Single Condition Logical Tests: 1) PivotTable, 2) Worksheet Formulas, and 3) Power Query (Grouping feature in Power Query at 25:55)
  • AND Logical Tests: AND function, Spilled Array and FILTER function
  • Boolean Math Operators and Math Operators: AND = Multiplication, OR = Addition
  • AND Logical Test in FILTER function
  • AND Logical Tests using Any Non-Zero Number as TRUE
  • COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, MAXIFS with an AND Logical Test.
  • Mixed Cell Reference For Conditionally Formatting a Row.
  • AND Logical Tests in Cross Tabulated Reports, Worksheet Formulas and PivotTable
  • AND Logical Tests in Quality Control using COUNTIFS function
  • Between Logical Test to create Monthly Sales Report and Frequency Distribution
  • OR Logical Tests on One Column and Two Columns: OR function, Spilled Formula, Average formula and FILTER function
  • XMATCH function to run an OR Logical test and a Compare Lists Logical Test. Spilled Array, FILTER function.
  • Adding with an OR Logical Test using SUMIFS inside SUM function
  • Conditional formatting a Row with an OR Logical Test
  • NOT Logical Test in Spilled Array and COUNTIFS function
  • Contains Logical Test in Spilled Array and COUNTIFS function
  • IF Function
  • IF and IS functions to create checkbook ledger
  • IFS function to create label in an Income Statement Report
  • Power Query with Complex Logical Test to extract records from a csv file
  • Complex Logical Tests to assess Custom Credit Worthiness in an Accounts Receivable Department. See AND and OR functions, Spilled Boolean Math Formula, IF function and FILTER function
  • (01:47:22) 9 Amazing Homework Problems
  • (01:47:33) Summary and Conclusions
  • (01:47:59) Closing and Video Links