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.
Syntax
If(logical_test, [value_if_True], [Value_if_false])
How to apply
- Let’s take a data set for calculating the commission on sales targets.
- You have conditions:
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%))
- 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.
Syntax
=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
=If(F2>=70,”Distinction”,IF(F2>=60,”CREDIT”,IF(G2>50,”PASS”,”FAIL”)))
How to apply
- Take a dataset of students.
- 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.
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:
