Deprecated: Creation of dynamic property ternplugin\youtube_video::$post is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/plugin/src/class/youtube_video.php on line 64

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

Posted on by Annie D

Download Excel file: https://excelisfun.net/files/07-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/07-M365ExcelClass.pdf
Download csv file for Power Query example: https://excelisfun.net/files/9112021-2022CallData.csv
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
Topics in video:
1. (00:00) Introduction
2. (01:00) What is a Logical Test in Excel?
3. (04:30) Examples of six most common types of logical tests with Filter Feature and PivotTable
4. (04:30) Examples of Single Condition, Contains and NOT Logical Tests
5. (08:44) Description of AND Logical Test and OR Logical Test
6. (17:16) Description of a Complex Logical Test
7. (20:09) Comparative Operators
8. (21:09) Comparative Operators Examples
9. (21:25) IS functions
10. (21:56) Boolean Values
11. (22:34) Single Condition Logical Tests: 1) PivotTable, 2) Worksheet Formulas, and 3) Power Query (Grouping feature in Power Query at 25:55)
12. (30:50) AND Logical Tests: AND function, Spilled Array and FILTER function
13. (34:05) Boolean Math Operators and Math Operators: AND = Multiplication, OR = Addition
14. (37:26) AND Logical Test in FILTER function
15. (38:52) AND Logical Tests using Any Non-Zero Number as TRUE
16. (41:01) COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, MAXIFS with an AND Logical Test.
17. (44:50) Mixed Cell Reference For Conditionally Formatting a Row.
18. (50:15) AND Logical Tests in Cross Tabulated Reports, Worksheet Formulas and PivotTable
19. (55:53) AND Logical Tests in Quality Control using COUNTIFS function
20. (58:10) Between Logical Test to create Monthly Sales Report and Frequency Distribution
21. (01:09:35)OR Logical Tests on One Column and Two Columns: OR function, Spilled Formula, Average formula and FILTER function
22. (01:18:44) XMATCH function to run an OR Logical test and a Compare Lists Logical Test. Spilled Array, FILTER function.
23. (01:21:43) Adding with an OR Logical Test using SUMIFS inside SUM function
24. (01:21:30) Conditional formatting a Row with an OR Logical Test
25. (01:26:36) NOT Logical Test in Spilled Array and COUNTIFS function
26. (01:28:19) Contains Logical Test in Spilled Array and COUNTIFS function
27. (01:31:23) IF Function
28. (01:34:25) IF and IS functions to create checkbook ledger
29. (01:36:27) IFS function to create label in an Income Statement Report
30. (01:38:45) Power Query with Complex Logical Test to extract records from a csv file
31. (01:41:48) 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. (01:47:22) 9 Amazing Homework Problems
33. (01:47:33) Summary and Conclusions
34. (01:47:59) Closing and Video Links

Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/ . Artist: http://audionautix.com/

Further Help

I offer limited consulting services to potentially assist you with data challenges, whether it's designing a complex Excel formula, writing a macro or building a whole new process for data capture, modeling and analysis.  Contact me if you have a need.

ExcelIsFunSun, October 2, 2022 5:38pm