Excel Logical Test Using Multiple If Statements in Excel [AND/OR]

To test certain conditions, people use If statement. The If() function is used to perform logical tests and to evaluate conditions that will show two outcomes.

This Excel IF statement is used to analyze a condition that will show two outcomes. Basically, excel logical test is used to evaluate the condition whether it is true or false.


Excel Logical Test

An IF statement is mainly used to test any condition that will give you more than two outcomes. If you are using a single If then it will give you two outcomes.

For more than two outcomes you need to use If statement inside another If statement.

Multiple If Statement in Excel for Three Outcomes

For multiple situations, you have to apply multiple If statements to get more than two outcomes. This means that you are creating if function within another if function.


If(logical_test, [value_if_True], [Value_if_false])

How to apply

Less than $300: Receive 7% commission

Sales btw $300 to $600: Receive 12.5% commission

Sales more than $600: Receive 16% commission

Now to find commission, we have to apply If statement. You must use nested if to calculate the commission of all salespeople.

  • To calculate commission, the formula is “=IF(B2<300,B2*7%, IF(B2<600,B2*12.5%,B2*16%))Excel-logical-test-1
  • After finding the commission, you need to press the enter key and then calculate the commission.
  • Using the Autofill feature, you can calculate the commission of the remaining person.

Hence, in this situation, the IF statement will check whether the value in B2 is greater or less than $300. If it is so, then the commission is 7%. If B2 is between $300 to $700 then the commission will be calculated at 12.5% and if the figure is more than $700, then the commission will be calculated via 16% rate.

Multiple If Statement in Excel for Four Outcomes

In these four outcomes, you require three different conditions. Excel If statement multiple conditions range tests a single condition and returns an outcome.


=IF(Condition A, Output Y, IF (Condition B, Output Z, IF (Condition C, Output R, Output S))))

In this formula, we have three conditions that will show 4 outcomes.

We start with, testing If statement A is true or not. If it is true then it will return output B

But if the condition is False then testing of B condition will start

If condition B is true, then the output will be Z and if it is false, the testing of condition C will begin.

If condition C is true then it returns R else S.

Let us take the situation and conditions

  • Average>=70 : Distinction grade
  • Average>=60 : Credit
  • Average>=50 : Pass
  • Average<50 : Fail

Syntax to find the grade is


How to apply

  • Take a dataset of students.Excel-logical-test-2
  • Along with the name, type the numbers that they get in each subject.
  • Calculate the average for every student.
  • On the basis of average marks, calculate the grade of the student.
  • For calculating the grade, you have to take the above condition.
  • In the grade section, write the formula for grade and press enter.
  • After you get the outcome in cell G2, drag the cell border below.Excel-logical-test-4

This is how you will be going to perform excel logical test on multiple conditions to get different outcomes.

Other Excel tutorials you may find useful:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment