[MUSIC] Welcome to the logical functions module. In this module, we will learn some simple and very useful functions Excel offers to evaluate conditional logic. The functions we will cover in this module are a subset of the logical functions provided by Excel. We will share references to where you can learn more about logical functions in Excel as part of the materials provided with this module. We encourage course participants to study these powerful functions independently to master their use as they are foundational building blocks in spreadsheet analysis. Let's review the functions we will cover in this module. The CountIfs function allows you to count the number of occurrences of a value in a range based on specific criteria. It takes as parameters the range in which you wish to count values and the criteria against which to apply to those values in order to make the count. The SumIfs function allows you to sum values in a range based on specific criteria. SumIfs takes, as parameters, first the range of values which you care to sum. Then, arrange with values you'd like to apply criteria against when summing, and lastly criteria to apply against this range. You'll note that you can feed any number of criteria ranges and criteria into SumIfs, which makes it quite powerful. The IF function allows you to compare different values or variables and return a specified value depending on the outcome of that test. The test must be structured as a boolean or true or false test. If no outputs are specified, the function will return true if the test is true and false, if the test is false. IF requires, as parameters, firstly the logical test, then the value if that test is true and lastly the value if that test is false. Combining And with the If function allows multiple logical tests in evaluating values in a data set. It is useful when we want to do something only if all of our tests are true and if we want to do something else if any of our tests is false. Each function within the And function must be structured as a boolean test. You can't simply list the values being compared. The And function itself can be nested to run more advanced tests. We'll talk more about If and And in conjunction as we work through the problems of this modular. Combining the If + Or functions allows multiple logical tests in evaluating a data set. This is useful when we want to do something when any of the tests is true and something else when all of the tests are false. Each function within the OR function must be structured as a unique test. You can't simply list the values being compared. And again, we'll come back to specific examples of using If and Or during the problem sets for this module. Lastly, combining, or nesting, multiple If functions can be a powerful way to evaluate chains of conditional logic. We can think of this as If a given test is true, then do this. Otherwise, If our first test is true and another test is true, then do something else. Otherwise, because none of the previous tests is true and then do this other thing. Nested If functions can incorporate additional formulas as parameters. We'll unlock some of the powerful uses for nested If functions as we work through the problems in this module. Let's begin with the spreadsheet accompanying this module. Before we dig into the exercises associated with the module, let's take a moment to explore the data set underlying all these problems. You see here that we have 11 columns and just over a hundred or so records that comprise sales records for a fictitional company across sales channels for their various products throughout a period of time. One important thing to note about this dataset is that it is structured formally as an Excel table. One thing this provides us is convenient references when referring to data in that table. Rather than using column letters and row numbers, we can use the name of the table, and special syntax, to refer to the names of each column. These special references are called structured references. We will provide additional reading material for you, so that you can learn more about this feature in Excel. Structured references and named ranges in general, significantly increased the readability of our spreadsheets. We encourage you to incorporate these in your own spreadsheet analysis. [MUSIC]
