en | de
Worldwide Content
Oct 5, 2022 6:00 AM

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

## 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