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

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