# Performing logical and conditional calculations in Excel 365 (2024)

Excel 365 (2024)    |    Intermediate
• 9 videos | 44m 9s
• Includes Assessment
• CPE
Rating 5.0 of 1 users (1)
Enhance your data analysis skills with our in-depth course on advanced formula techniques for Excel 365. In this course, discover how to build powerful nested formulas with logical and conditional functions, helping you to tackle complex problems with ease. First, learn to optimize your workflow by combining different logical functions and using the IFS and SWITCH functions for effective decision-making processes. Understand how to sum up data based on specific conditions and use conditional average functions to get useful insights from your datasets. Next, discover important techniques for counting data with conditions, using the MAXIFS and MINIFS functions to find maximum and minimum values within given criteria. Moreover, you'll learn the LET function make your calculations simpler and faster. This course aligns with the objectives of Exam MO-211: Microsoft Excel Expert (Microsoft 365 Apps). Would you like hands-on practice? Go to the Course Contents pane and download the sample files to follow along with select videos.

## WHAT YOU WILL LEARN

• Discover the key concepts covered in this performing logical and conditional calculations in excel 365 (2024) course
Create complex formulas by combining functions in excel 365
Use if, or, and not in conditional formulas
Use the ifs and switch functions to test multiple conditions
Use sumif and sumifs to get totals based on one or multiple criteria
• Use averageif and averageifs to get averages based on one or multiple criteria
Use countif and countifs to count cells that meet one or more criteria
Use the minifs and maxifs functions to calculate the smallest and the largest numbers that meet one or more criteria
Use the let function to create variables in a formula

## IN THIS COURSE

• This video outlines the key content covered in this Performing logical and conditional calculations in Excel 365 (2024) course, including how to create nested formulas, using the IFS and SWITCH functions, and learning how to use the new LET function.
• In Excel 365, nested formulas allow to combine two or more functions to perform logical calculations. In this video, learn how to use the IF and AND functions to test two or more conditions in order to return one or more results. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform logical operations by using nested functions including the IF(), IFS(), SWITCH(), SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS(), AND(), OR(), NOT(), and LET() functions.
• 3.  Combining logical functions in Excel 365
In Excel 365, you can perform logical operations, such as using the NOT function, to check if one value is not equal to another. You can even combine qualifying and alternative conditions such as combining the OR and IF functions in a formula. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform logical operations by using nested functions including the IF(), IFS(), SWITCH(), SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS(), AND(), OR(), NOT(), and LET() functions.
• 4.  Using the IFS and SWITCH functions in Excel 365
The IFS function in Excel simplifies checking multiple conditions by returning the result for the first true one, thereby replacing complex nested IF constructs. The SWITCH function assesses a single value against various cases, delivering the associated outcome for a match, and includes a default return option when no matches are present. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform logical operations by using nested functions including the IF(), IFS(), SWITCH(), SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS(), AND(), OR(), NOT(), and LET() functions.
• 5.  Calculating totals with conditions in Excel 365
In Excel 365, use the SUMIF and SUMIFS functions to perform a conditional sum calculation on a cell range based on certain criteria being met. In this video, explore how to insert and use conditional SUM formulas in Excel 365. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform logical operations by using nested functions including the IF(), IFS(), SWITCH(), SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS(), AND(), OR(), NOT(), and LET() functions.
• 6.  Using conditional average functions in Excel 365
If you need to calculate the average in a certain range but have a criteria to apply, you can use the AVERAGEIF function. Excel also proposes the AVERAGEIFS function that calculates the average in a range if you have more than one criteria. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform logical operations by using nested functions including the IF(), IFS(), SWITCH(), SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS(), AND(), OR(), NOT(), and LET() functions.
• 7.  Counting data with conditions in Excel 365
You can use the COUNTIFS function when you need to count the number of cells that meet one or more criteria. With COUNTIFS, you can apply more than one criteria in more than one range. Excel also offers the NOT function, that returns the opposite of a logical value. Combined with the IF function you can determine, for example, the availability of a product. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform logical operations by using nested functions including the IF(), IFS(), SWITCH(), SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS(), AND(), OR(), NOT(), and LET() functions.
• 8.  Using the MAXIFS and MINIFS functions in Excel 365
The MINIFS and MAXIFS functions allow you to find the minimum and maximum value in a range after applying one or more criteria. You can use logical operators and wild card for partial matching if necessary. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform logical operations by using nested functions including the IF(), IFS(), SWITCH(), SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS(), AND(), OR(), NOT(), and LET() functions.
• 9.  Using the LET function in Excel 365
The LET function in Excel allows you to assign names to calculation results. This can help you simplify complex formulas, make them more readable, and potentially increase their calculation speed by avoiding redundant calculations. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Perform logical operations by using nested functions including the IF(), IFS(), SWITCH(), SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS(), AND(), OR(), NOT(), and LET() functions.

## EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE

Skillsoft is providing you the opportunity to earn a digital badge upon successful completion on some of our courses, which can be shared on any social network or business platform.

Digital badges are yours to keep, forever.

## YOU MIGHT ALSO LIKE

Rating 4.6 of 416 users (416)
Rating 4.5 of 449 users (449)