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

This video is a comprehensive lesson in logical formulas and functions and conditional calculations in the Excel Worksheet and Power Query.

This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW

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/

#MikeGirvin
#excelisfun
#MikeexcelisfunGirvin
#Microsoft365Excel
#MECS07
#Microsoft365ExcelCompleteStory
#LogicalTest
#BoolenTests
#BoolenMath
#ANDfunction
#LogicaltestsinWorksheetFormulas
#ORfunction
#FILTERfunction
#ISNUMBERfunction
#IFfunction
#IFSfunction
#BasicsofLogicalTests
#FundamentalsofLogicalTest
#SEARCHfunction
#XMATCHfunction
#ANDlogicalTest
#ORlogicalTest
#ContainsLogicalTest
#CompareTwoLists
#FreeClass
#FreePowerBIClass
#excelformulas
#excelfunctions
#exceltips
#exceltricks
#sumifs
#countifs
#logical
#logicaltests
#logicalfunction
#conditionalstatements
#ifs
#if
#ifformula
#and
#or