Test Whether a Number Falls Between Two Values

Excel & Google Sheets

How it works

Spreadsheets have no single "between" operator, so you test each end separately and let AND require both: at least 10 AND at most 20. AND returns TRUE only when every condition inside it passes, and IF converts that into your labels. The operators define whether the boundaries count — >= and <= include exactly 10 and exactly 20, while > and < would exclude them. Decide that before you write the formula; it's the difference between a 10-unit order getting the discount or not.

B2>=10

The lower bound. >= means exactly 10 counts as in range.

B2<=20

The upper bound. <= means exactly 20 counts too.

AND(…)

TRUE only when both bounds pass — the number sits inside the window.

When to use it

Use it for quantity discount bands, acceptable tolerance ranges on measurements, invoice amounts within an approval threshold, or response times inside an SLA window.

Common mistakes

• ✕Writing 10<=B2<=20 like math.

  ✓Spreadsheets evaluate that left to right: 10<=B2 becomes TRUE or FALSE, which is then compared to 20 — silently wrong on every row. Always split it: AND(B2>=10,B2<=20).

• ✕Boundary values land on the wrong side.

  ✓AND(B2>10,B2<20) rejects an order of exactly 10 or 20. If the ends should count, use >= and <= — say the rule out loud ("10 through 20 inclusive") before picking operators.

• ✕Bounds accidentally reversed.

  ✓AND(B2>=20,B2<=10) can never be true, so everything shows Out of range. The smaller number takes >=, the larger takes <=.