Excel Agility: Logic Functions - IF, AND, OR, and Beyond
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Taxation and Accounting, Software
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
In this valuable course, Excel expert David Ringstrom, CPA, briefly reviews the IF function and then takes you step-by-step beyond the basics. He explains what can go awry with the IF function and ways to improve the integrity of decision-making formulas. David shares alternatives to the IF function, including IFS, CHOOSE, VLOOKUP, SUMIF, and others. David’s assertion is that if you’re nesting more than a couple of IF functions, there’s probably a more refined and resilient approach you can take.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples used in the course.
Who Would Be Interested in This Course:
Practitioners who would benefit by learning more about Excel’s logic functions and decision-making formulas.
Your Benefits of Attending:
- Employing the SUBSTITUTE function as an alternative to complex formulas based on the IF function.
- Employing the IFERROR function for situations where VLOOKUP returns #N/A.
- Testing for two or more conditions at once with Excel’s AND function.
- Learning about the MINIFS function available in certain versions of Excel.
- Avoiding the complexity of nested IF statements with Excel’s CHOOSE function.
- Using the SUMIFS function to sum values based on multiple criteria.
- Using the COUNTIF function to determine the number of times an item appears in a list.
- Understanding when you might wish to use ISERROR or ISNA instead of IFERROR.
- Incorporating comparison operators within SUMIF to sum numbers based on range criteria, such as greater than, less than, and so on.
- Reconstructing spreadsheet data to use VLOOKUP as a better alternative to nesting IF functions.
- Comparing two cells directly to return TRUE or FALSE if they’re exactly the same or not without using IF.
- Viewing the pros, cons, and limitations of nesting IF functions.
Learning Objectives:
- List several alternatives to the IF function.
- Recall how to test for two or more conditions simultaneously with Excel’s AND function.
- Define when to use the ISERROR function or the ISNA function vs. the IFERROR function.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience Working with Excel Functions Recommended
Advance Preparation:
None
Introduction
Excel Versions 00:01:07
Introduction to IF 00:03:08
Comparing Cells Without Using IF 00:06:57
Two Levels of Nested IF Statements 00:13:54
Multiple Levels of Nested IF Statements 00:18:45
Use F9 to Calculate Part of a Formula 00:21:02
Evaluate Formulas 00:25:31
IFS Function (Excel 2019+) 00:33:28
Introduction to the CHOOSE Function 00:38:57
Introduction to IFERROR Function 00:42:24
VLOOKUP as Alternative to IF 00:47:27
IFERROR/VLOOKUP Alternative to IF 00:52:31
Introduction to IFNA (Excel 2013+) 00:55:12
Introduction to IS Functions 01:02:37
Correcting Numbers Stored as Text 01:06:00
Introduction to AND 01:07:07
Introduction to OR 01:09:29
Introduction to XOR 01:10:49
Introduction to NOT 01:12:02
SUBSTITUTE as Alternative to IF 01:12:31
Introduction to SUMIF 01:14:56
SUMIF with Comparison 01:18:03
Introduction to SUMIFS 01:19:45
SUMIFS with Two Range Criteria 01:21:42
Introduction to SUMPRODUCT 01:23:19
SUMPRODUCT as SUMIFS Alternative 01:26:54
Introduction to COUNTIF/COUNTIFS 01:32:38
MIN/MAX and SMALL/LARGE 01:34:10
MAXIFS Function (Excel 2019+) 01:36:48
MINIFS Function 01:39:15
Conclusion 01:44:59
- AND Function 01:07:07
- Array Formula 01:39:30
- AutoSum 00:45:26
- CHOOSE Function 00:38:57
- COUNTIF 01:32:38
- COUNTIFS 01:32:38
- Evaluate Formulas 00:25:31, 00:26:59
- IFERROR Function 00:42:24, 00:52:31
- IF Function 00:03:12, 01:12:31
- IFNA Function 00:55:12
- IFS Function 00:33:52
- ISERROR 00:45:46
- IS Functions 01:02:37
- LARGE Function 01:34:15
- LEN Function 00:09:08
- MAX Function 01:34:11
- MAXIFS Function
- MIN Function 01:34:12
- MINIFS Function 01:39:15
- NOT Function 01:12:02
- OR Function 01:09:29
- SMALL Function 01:34:14
- SUBSTITUTE Function 01:13:20
- SUMIF Function 01:15:04, 01:18:03
- SUMIFS Function 01:19:50
- SUMPRODUCT 01:23:19
- Table Array 00:48:23
- VLOOKUP Function 00:47:31,00:52:31
- XOR Function 01:10:49
AND Function : The Microsoft Excel AND function returns TRUE if all conditions are TRUE. It returns FALSE if any of the conditions are FALSE. The AND function is a built-in function in Excel that is categorized as a Logical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the AND function can be entered as part of a formula in a cell of a worksheet.
Array Formula: In Excel, an Array Formula allows you to do powerful calculations on one or more value sets. The result may fit in a single cell or it may be an array. An array is just a list or range of values, but an Array Formula is a special type of formula that must be entered by pressing Ctrl+Shift+Enter.
AutoSum: The AutoSum feature appears on both the Home menu and the Formulas menu as a Greek sigma symbol. When you click AutoSum, or press Alt-= Excel adds a sum function to the current cell or cells that you've selected.
CHOOSE Function : The CHOOSE function allows you to return a specified item from a list, but in certain cases, it also can be used to have VLOOKUP return data from the left of its criteria column.
COUNTIF: Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition. For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify.
COUNTIFS: The COUNTIFS function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. The COUNTIFS function allows you to stipulate multiple criteria, hence the plural.
Evaluate Formulas : A feature available on the Formulas tab of Excel 2007 and later and the Tools/Formula Auditing menu in Excel 2003 and earlier that allows you to step through a formula in slow motion.
IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
IFNA Function : Introduced in Excel 2013, the IFNA function allows users to display alternative results for a calculation that results in a #N/A error. The IFNA function will, however, reveal other errors, such as #REF!, #NULL!, etc. IFNA isn’t backward compatible with Excel 2010 and earlier.
IFS Function: The IFS formula in excel 2019 checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition.
IS Functions: The IS Functions return a TRUE or FALSE value based upon a condition they evaluate. They can detect errors, blank cells, text, numbers...etc. They are critical in the development of dashboard interfaces and are heavily used with the IF function.
ISERROR: The ISERROR function checks whether a value is an error and returns TRUE or FALSE. The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message or run a different calculation when found.
LARGE Function: The =LARGE function can be used to return the largest, second largest, third largest and kth largest values from a range.
LEN Function: The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.
MAX Function: The Microsoft Excel MAX function returns the largest value from the numbers provided. The MAX function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the MAX function can be entered as part of a formula in a cell of a worksheet.
MAXIFS Function: The Excel MAXIFS function returns the largest numeric value that meets one or more criteria in a range of values. MAXIFS can be used with criteria based on dates, numbers, text, and other conditions. MAXIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
MIN Function: The Microsoft Excel MIN function returns the smallest value from the numbers provided. The MIN function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel.
NOT Function: You use the NOT function in Excel to reverse the value of its argument. In other words, if logical evaluates to FALSE, the NOT function returns TRUE and vice versa.
OR Function : The OR function is a logical function to test multiple conditions at the same time. OR returns either TRUE or FALSE.
SMALL Function: Use this function to return values with a particular relative standing in a data set.
SUBSTITUTE Function: The Excel SUBSTITUTE function replaces text in a given string by matching. SUBSTITUTE is case-sensitive and does not support wildcards.
SUMIF: A look-up function in Excel that allows you to add up numbers based upon a criterion that you specify. Unlike VLOOKUP, the SUMIF function can add up two or more values and returns zero (instead of #N/A) if no match is found.
SUMIFS Function : A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS function can add up two or more values and returns zero (instead of #N/A) if no match is found.
SUMPRODUCT: The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.
Table Array: A table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP. For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data. For HLOOKUP (horizontal lookup), the table_array must contain at least two rows of data.
VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.
XOR: The XOR function performs what is called "exclusive OR". With two logical statements, XOR returns TRUE if either statement is TRUE, but returns FALSE if both statements are TRUE. If neither is TRUE, XOR also returns FALSE.
