Excel Logical Take a look at Utilizing A number of If Statements in Excel [AND/OR]
To check sure circumstances, folks use If assertion. The If() operate is used to carry out logical checks and to guage circumstances that can present two outcomes. This excel if assertion is used to research a situation that can present two outcomes. Mainly, excel logical take a look at is used to guage the situation whether or not it’s true or false.
Excel Logical Take a look at
If assertion is principally used to check any situation that will provide you with greater than two outcomes. In case you are utilizing a single If then it will provide you with two outcomes. For greater than two outcomes you want to use If assertion inside one other If assertion.
A number of If Assertion in Excel for Three Outcomes
For a number of conditions, you need to apply a number of If assertion to get greater than two outcomes. Which means that you might be creating if operate inside one other if operate.
If(logical_test, [value_if_True], [Value_if_false])
Learn how to apply
- Lets take a knowledge set for calculating fee on gross sales goal.
Lower than $300: Obtain 7% fee
Gross sales btw $300 to $600: Obtain 12.5% fee
Gross sales greater than $600: Obtain 16% fee
Now to seek out fee, we’ve got to use If assertion. You need to use nested if to calculate fee of all salesperson.
- To calculate fee, the components is “=IF(B2<300,B2*7%, IF(B2<600,B2*12.5%,B2*16%))
- After discovering the fee, you want to press enter key after which calculate the fee.
- Utilizing Autofill function, you may calculate the fee of remaining particular person.
Therefore, on this state of affairs, if assertion will examine whether or not the worth in B2 is bigger or lower than $300. If it’s so, then the fee is 7%. If B2 is in between $300 to $700 then the fee shall be calculated at 12.5% and if the determine is greater than $700, then the fee shall be calculated through 16% fee.
Test this Excel examine two columns
A number of If Assertion in Excel for 4 Outcomes
On this 4 outcomes, you require three totally different circumstances. Excel If assertion a number of circumstances vary checks a single situation and returns an final result.
=IF(Situation A, Output Y, IF (Situation B, Output Z, IF (Situation C, Output R, Output S))))
On this components, we’ve got three circumstances that can present four outcomes.
We begin with, testing If assertion A is true or not. Whether it is true then it would return output B
But when the situation is False then testing of B situation will begin
If situation B is true, then the output shall be Z and whether it is false, the testing of situation C will start.
If situation C is true then it returns R else S.
Allow us to take the state of affairs and circumstances
- Common>=70 : Distinction grade
- Common>=60 : Credit score
- Common>=50 : Go
- Common<50 : Fail
Syntax to seek out the grade is
Learn how to apply
- Take a dataset of scholars.
- Together with the identify, kind the numbers that they get in every topic.
- Calculate the common of each scholar.
- On the idea of common marks, calculate the grade of the coed.
- For calculating the grade, you need to take the above situation.
- Within the grade part, write the components for grade and press enter.
- After you get the end result in cell G2, drag the cell border under.
That is how you can be going to carry out excel logical take a look at on a number of circumstances to get totally different outcomes.