Check a value is within limits
PremiumIn E2, show "OK" if the measurement in B2 is between the limits in C2 and D2, both included, and "Check" if not. Both tests must be true, so use AND inside IF.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Part | Measured | Min | Max | Result | |
| 2 | Bolt A | 10.1 | 9.5 | 10.5 | ||
| 3 | Bolt B | 10.6 | 9.5 | 10.5 | ||
| 4 | Washer | 2 | 1.8 | 2.2 | ||
| 5 | Nut | 1.7 | 1.8 | 2.2 | ||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock AND and OR and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for job interviews
Why this works
A value is within limits when it is at least the minimum and at most the maximum. Bolt A measures 10.1, which is at least 9.5 and at most 10.5, so both tests are TRUE, AND returns TRUE and IF shows OK. Filled down, Bolt B is over its maximum, the washer is OK and the nut is too small.
Common mistake: Writing the limits as one chain, =C2<=B2<=D2. Excel compares the first pair, gets TRUE or FALSE, and then compares that with D2.
The answer
- E2
=IF(1AND(B2>=C2,B2<=D2),2"OK",3"Check")
In plain words: 1If the measurement in B2 is between the limits in C2 and D2, 2show OK. 3Otherwise show Check.