How to use IF with AND function..?
In our previous post IF function and nested IF function is explained with the example. Similarly, instead of using Nested IF function IF with AND formula can be used for testing multiple logical tests to give the results.
In AND logic, different logical test can be done. This function will check all conditions and returns TRUE if all are correct and FALSE if any of the logical test fails.
In above example it will check whether B3 is greater than 1 and B4 is greater than 2 . If both are correct it will give the result TRUE.
This AND function can be used with IF to get the customised result instead of TRUE of FALSE. When IF function is used the result can be a constant, a cell reference or a formula result.
Syntax for IF with AND formula:
=IF(AND(LOGICAL1,LOGICAL2),OUTPUT IF TRUE,OUTPUT IF FALSE)
In AND function you can give up to 256 logical tests.
This combination of IF AND formula is explained with one example:
In this example the marks obtained by the student in 6 subject is checked. Result is given whether Pass or Fail
If the student obtains 40 or more marks in each subject and the total marks obtained is 300 or more then the result is Pass.
If the student obtains less than 40 marks in any of the subject and total is less than 300 then the result is Fail.
To check this following IF and AND function combined formula is used.
The formula will check marks of all 6 subject and also the total marks with AND function, if result is true the formula will give the output of Pass, else it will give the output of Fail.
If you find any difficulty in applying this formula, you can comment.
Similarly you can also try IF function with OR instead of AND. We will explain the same in our next post.