AND Function in Excel Definition and Formula:
AND function in Excel is used to check if all the logical conditions are true or not. ‘TRUE’ returns if all the conditions correctly match.
Formula– AND(logical1, [logical2], …….)
Explanation with Example Questions:
Like the way Brenda taught the last concept of OR function in excel, she decides to teach the use of AND function in excel. She gave Carl and Carly some questions to solve to learn the concept.
- Is the amount received greater than $195?
- Which Bakery paid the most amount?
- Which Bakery paid the least amount?
Remember when all the conditions satisfy ‘TRUE’ will be the output else ‘FALSE’.
Now, coming to the answers to the questions
Is the amount greater than $195 or not?
Formula: AND(E4>195). After writing this formula in cell F4 then drag from the bottom right of F4 to the bottom of the column.
When the formula is applied, we get TRUE if the value is >$195 else FALSE.
Which Bakery paid the most amount?
Formula: AND(E4>E5,E4>E6,E4>E7,E4>E8,E4>E9,E4>E10)
In this formula, we compared the E4 with all the other values and we can see E4 i.e. 145 is not greater than every other value; the value we get as output is ‘FALSE’.
When the formula is applied, we get ‘TRUE’ for ‘Mrs Kim Bakery’ as they paid the most amount and ‘FALSE’ for all other bakeries.
Which Bakery paid the least amount?
Formula: AND(E4<E5,E4<E6,E4<E7,E4<E8,E4<E9,E4<E10)
In this formula, we compared the E4 with all the other values and we can see E4 i.e. 145 is smaller than other values; the value we get as output is ‘TRUE’.
When the formula is applied, we get ‘TRUE’ for ‘Best Bakers’ as they paid the least amount and ‘FALSE’ for all other bakeries.
Brenda also explained the benefits of using AND function in Excel over the IFS function.
- We get a definite output when we compare items in AND function like TRUE or FALSE, while in the ‘IFS function’ if all the conditions are not satisfied, we get NA.
After dinner, Brenda sits in front of the fireplace and thinks about tomorrow’s cake orders. Another day ends with another great Excel concept.