Excel Training – Build a nested IF

Welcome to this training-nested IF exercise, which is designed to improve your skills in this essential Excel situation. An IF function handles two situations: when a logical test is TRUE or FALSE. But when you need more cases, you must use multiple IF functions.

In this training, you will learn how to calculate employee bonuses based on multiple criteria.

1. Start with a basic IF function.
2. Add more criteria based on employee categories.
3. Learn how to use the IFS function for easier formulas.
4. Understand the trick at the end of the IFS function.

Click the button below to start your training nested IF exercise! 😀

 

Start

Question

We want to build a formula for giving a bonus to our employees

• 1000 for Managers
• 500 for all the other employee categories.

Which of the following formulas does this job?

 

 

=IF("Manager",1000,500)

=SUMIF(B2:B13,1000,500)

=IF(B2="Manager",1000,500)

=IFS("Manager",1000,500)

Question 1 of 7

Question

Now, we want to add a new condition:

• Still 1000 for the Manager
• 200 for the Worker Category
• 500 for all the other Categories (like Supervisor and Internship)

How do you write such a formula?

 

=IF(B2="Manager",1000,IF(B2="Supervisor",500,200))

=IF(B2="Manager",1000&AND(B2="Supervisor",500,200))

=IF(B2="Manager",1000,AND(B2="Supervisor",500,200))

=IF(B2="Manager",1000,B2="Supervisor",500,200)

Question 2 of 7

Question

What is the Excel 365 function that allows you to manage multiple conditional steps in a single formula, unlike the IF function which handles only one logical test?

IFS

MAXIFS

COUNTIFS

MULTIPLE.IF

Question 3 of 7

Question

How do you write the IFS function to return the value

• 1000 for the Manager
• 500 for the Supervisor
• 200 for the Worker

 

 

As you can see, the formula returns errors sometimes but that's a part of the training, don't worry.

=IFS(B2="Manager",1000,(B2="Supervisor",500,B2="Worker",200))

=IFS(B2="Manager",1000,B2="Supervisor",500,200)

=IFS(B2="Manager",1000+B2="Supervisor",500+B2="Worker",200)

=IFS(B2="Manager",1000,B2="Supervisor",500,B2="Worker",200)

Question 4 of 7

Question

How do you write the last IFS argument for "all the other cases"?

• 1000 for Manager
• 500 for Supervisor
• 200 for all the other employees

 

=IFS(B2="Manager",1000,B2="Supervisor",500,NOT,200)

=IFS(B2="Manager",1000,B2="Supervisor",500,TRUE,200)

=IFS(B2="Manager",1000,B2="Supervisor",500,ALL,200)

=IFS(B2="Manager",1000,B2="Supervisor",500,LET,200)

Question 5 of 7

Question

How do you write the IFS function to manage 4 conditions?

• 1000 for Manager
• 500 for Supervisor
• 200 for the worker
• 0 for the other employee category

=IFS(TRUE,0,B2="Manager",1000, B2="Supervisor",500,B2="Worker",200)

=IFS(B2="Manager",1000, B2="Supervisor",500,TRUE,0)

=IFS(B2="Manager",1000, B2="Supervisor",500,B2="Worker",200,TRUE,0)

=IFS(B2="Manager",1000, B2="Supervisor",500,B2="Worker",200,TRUE)

Question 6 of 7

Question

Now we want to exclude from the bonus the employee with less than 3 years of seniority. How do you write the IFS function?

• 1000 for Manager
• 500 for Supervisor
• 200 for the worker only with a seniority greater than 3 years (column C)
• 0 for the other employee category

=IFS(B2="Manager",1000,B2="Supervisor",500, AND(B2="Worker",C2>=3),200,TRUE,0)

=IFS(B2="Manager",1000,B2="Supervisor",500, AND(B2="Worker",C2>=3),200)

=IFS(B2="Manager",1000,B2="Supervisor",500, B2="Worker",200,C2>=3,200,TRUE,0)

=IFS(B2="Manager",1000,B2="Supervisor",500, OR(B2="Worker",C2>=3),200,TRUE,0)

Question 7 of 7

Your email address:

Your name:

 Loading...

 

Related Exercises

• Training Logical Test – Advanced Level
• Manage the inventory of your products (IF function)
• Test Your Skills with COUNTIF and COUNTIFS
• Step-by-step guide for SUMIF function
• Solve SUMIF Exercise: Answer Fun Questions

Excel-Tutorial.com, your training partner.

The purpose of Excel-tutorial.com is to help users master Excel through a series of tests, tutorials, and online training programs. Whether you're a beginner or an advanced user, our resources are designed to build your skills step by step.

Our certified experts have developed detailed training programs tailored to all skill levels. Our courses cover various Excel topics, from basic data processing to advanced data analysis and visualization. You'll find clear instructions, easy-to-follow lessons, and structured content that ensures steady progress.

Excel-tutorial.com stands out for its interactive teaching approach. Our lessons include exercises, real-life use cases, and engaging quizzes to solidify your understanding. This lets you apply your new skills immediately to real-world tasks, making learning both practical and impactful.

Plus, you can track your progress and test your knowledge with quizzes that provide instant feedback. We aim to help you gain confidence in Excel, one concept at a time. Join us at Excel-tutorial.com and start transforming your data skills today!