Chapter 5_M2

WORKING WITH FORMULAS AND FUNCTIONS CHAPTER - 5: USING FORMULAS FOR CONDITIONAL ANALYSIS Structure 1.0 Learning Objectives 1.1 Introduction 1.2 Conditional Formulas 1.3 Single condition - if functions 1.4 Single Condition - NOT, AND and OR Functions 1.5 Multiple Conditions - IF Function (nested) 1.6 Summary 1.7 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain What is Conditional Formulas in MS Excel?  Explain the Single condition - if functions in MS Excel  Explain the Multiple Conditions - IF Function (nested) in MS Excel 1.1 INTRODUCTION Excel provides several worksheet functions for performing conditional analysis. You'll use some of those functions in this chapter. Conditional analysis means performing different actions depending on whether a condition is met. A condition is a value or expression that returns TRUE or FALSE. Based on the value of the condition, a formula can branch into two separate calculations. That is, when the condition returns TRUE, one value or expression is evaluated while the other is ignored. A FALSE condition reverses the flow of the formula, and the first value or expression is ignored while the other is evaluated. Page 26 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.2 Condition Formulas If you want to perform a calculation when a cell contains a specific value you can create a conditional formula. A conditional formula returns one value if the condition is True and a different value if the condition is False. A conditional formula can be created in several different ways. There are 3 useful functions that can be used to create conditional formulas.  The IF function can return a value based on whether a certain condition is True or False.  The IFS function can return a value based on multiple conditions.  The SWITCH function can return a value based on a list of exact matches. 1.3 Single Condition - IF Functions You can use any combination of cell references, operators, constants and even other functions to create this logical test. The following Operators can be used to generate True or False values. These operators include: greater than (>), less than (<) and equal to (=). Page 27 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.4 Single Condition - NOT, AND and OR Functions These three functions are often included in the \"logical_test\" for slightly more advanced conditions.  Cell E3 - Return the opposite of a True or False value you would use the NOT function.  Cell E4 - Return the logical AND for two different conditions you would use the AND function.  Cell E5 - Return the logical OR for two different conditions you would use the OR function. 1.5 Multiple Conditions - IF Function (nested) If you want to check for multiple conditions you can use \"nested\" IF functions. When you nest the IF functions, you specify an IF function as the value of either of the \"value_if_true\" or \"value_if_false\". arguments You can combine or nest up to 64 levels of IF functions within one formula. Page 28 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  Multiple Conditions - IFS Function The IFS function can return a value based on multiple conditions. If you want to perform multiple conditions, instead of using nested IF functions you could use the IFS function.  Multiple Conditions - SWITCH Function The SWITCH function can return a value based on a list of exact matches. 1.6 Summary  Conditional analysis means performing different actions depending on whether a condition is met.  A condition is a value or expression that returns TRUE or FALSE. Based on the value of the condition, a formula can branch into two separate calculations.  A conditional formula returns one value if the condition is True and a different value if the condition is False.  When you nest the IF functions, you specify an IF function as the value of either of the \"value_if_true\" or \"value_if_false\".  The IFS function can return a value based on multiple conditions.  The SWITCH function can return a value based on a list of exact matches. Page 29 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.7 References https://bettersolutions.com/excel/formulas/conditional.htm https://support.microsoft.com/en-us/office/create-conditional- formulas-ca916c57-abd8-4b44-997c-c309b7307831 https://exceljet.net/formula/conditional-mode-with-criteria https://www.real-statistics.com/excel-capabilities/excel-conditional- functions/ https://www.oreilly.com/library/view/excel-2019- bible/9781119514787/c13.xhtml https://onlinelibrary.wiley.com/doi/10.1002/9781119549376.ch13 Page 30 of 63 All Rights Reserved. Vol. TLE001/03-2022