Dive into an extensive video tutorial on logical tests in Excel, covering a wide range of topics from basic concepts to advanced techniques. Learn how to use Excel formulas, conditional formatting, PivotTables, and Power Query to perform various logical operations. Explore single condition, AND, OR, and complex logical tests, as well as their applications in functions like COUNTIFS, SUMIFS, and FILTER. Discover how to implement logical tests in cross-tabulated reports, quality control processes, and financial analyses. Practice with real-world examples, including creating monthly sales reports, assessing credit worthiness, and extracting data from CSV files. Gain hands-on experience with downloadable Excel files and supplementary materials, and conclude with challenging homework problems to reinforce your newly acquired skills.
Logical Tests - Excel Formulas, Conditional Formatting, PivotTables, Power Query, and More
ExcelIsFun via YouTube
Overview
Syllabus
1. Introduction
2. What is a Logical Test in Excel?
3. Examples of six most common types of logical tests with Filter Feature and PivotTable
4. Examples of Single Condition, Contains and NOT Logical Tests
5. Description of AND Logical Test and OR Logical Test
6. Description of a Complex Logical Test
7. Comparative Operators
8. Comparative Operators Examples
9. IS functions
10. Boolean Values
11. 22:34 Single Condition Logical Tests: 1 PivotTable, 2 Worksheet Formulas, and 3 Power Query Grouping feature in Power Query at
12. AND Logical Tests: AND function, Spilled Array and FILTER function
13. Boolean Math Operators and Math Operators: AND = Multiplication, OR = Addition
14. AND Logical Test in FILTER function
15. AND Logical Tests using Any Non-Zero Number as TRUE
16. COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, MAXIFS with an AND Logical Test.
17. Mixed Cell Reference For Conditionally Formatting a Row.
18. AND Logical Tests in Cross Tabulated Reports, Worksheet Formulas and PivotTable
19. AND Logical Tests in Quality Control using COUNTIFS function
20. Between Logical Test to create Monthly Sales Report and Frequency Distribution
21. OR Logical Tests on One Column and Two Columns: OR function, Spilled Formula, Average formula and FILTER function
22. XMATCH function to run an OR Logical test and a Compare Lists Logical Test. Spilled Array, FILTER function.
23. Adding with an OR Logical Test using SUMIFS inside SUM function
24. Conditional formatting a Row with an OR Logical Test
25. NOT Logical Test in Spilled Array and COUNTIFS function
26. Contains Logical Test in Spilled Array and COUNTIFS function
27. IF Function
28. IF and IS functions to create checkbook ledger
29. IFS function to create label in an Income Statement Report
30. Power Query with Complex Logical Test to extract records from a csv file
31. 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
32. 9 Amazing Homework Problems
33. Summary and Conclusions
34. Closing and Video Links
Taught by
ExcelIsFun
Related Courses
-
Conditional Calculations with Excel Formulas - Comprehensive Lessons
-
Excel Formulas for Counting and Adding: COUNT, COUNTA, ROWS, SUM, SUMIFS, COUNTIFS and IF Functions - Part 2
-
Excel Spreadsheet Formulas - Old School or Dynamic Arrays?
-
Excel Statistical Analysis - AND, OR, and NOT Logical Tests for COUNTIFS & FILTER Functions
-
Comprehensive Excel Dynamic Array Formula Lesson - The Power of Array Formulas
-
Excel for Accountants - PivotTables, Power Query, IF, SUMIFS, VLOOKUP, Flash Fill, Charts Seminar
