There will be times when you need to compare 3 or more cells in Excel to generate a desired outcome. So if I am comparing 3 cells in Excel in order to see whether we should immediately hire a candidate or interview them first we could make use of the AND and OR formula.
So below we want to know whether we can
- immediately accept the student (if all marks are above the 75% required mark) or
- should interview them first (if ANY one of the marks are above the required 75%).
If you use an IF statement you are going to have to nest them as you will have to check IF B4>B2 then IF C4>C2 etc.
Better would be to use the AND function.
As shown below, all the AND function asks for is the logical test. Note that you can enter up to 255 logical tests! The end result of this is a TRUE or a FALSE depending on the result. You can then create an IF formula that shows a result depending on whether it is a TRUE or FALSE (learn more about the IF function here).
How to decide whether you need an AND or an OR in Excel
Often the most difficult thing about these functions is whether you should use an AND or an OR in the test. You can try and think it through but we find the best is to explain the test out loud and you will say the one that is required. For example in the above example I would explain the test as
If the candidates Maths mark is above 75% AND their Science mark is above 75% AND their English mark is above 75%, then hire immediately.
The test for an interview first is
if the candidates Maths mark is above 75% OR their Science mark is above 75% OR their English mark is above 75%, then hire immediately.
In this way you can hear which function to use. The OR function would be exactly the same except using OR.