The Fixing IF guide
An IF with no error can still be wrong. Read it out loud: the test, the value when the test is TRUE, the value when it's FALSE. Then check the row that sits exactly on the limit: =IF(B2>=20000, B2*8%, B2*5%).
The checklist
| Symptom | Cause | Fix |
|---|---|---|
| Exactly 20,000 gets the low rate | > instead of >= | B2>=20000 |
| Small sales get the high rate | The two results are swapped | TRUE value first, then FALSE value |
| Big sales never get the high rate | The limit is in quotes: "20000" | Remove the quotes |
| The cell shows FALSE | No third argument | Add the value for FALSE |
Why "20000" in quotes never matches
In quotes, 20000 is text. Excel ranks every number below any text, so B2>="20000" is FALSE for every sale, however big. The formula gives no error, which is why it goes unnoticed.
Formulas typed one by one
When each row has its own hand-typed IF, they drift apart. Fix one, check it on the edge case, then fill it down over the rest so every row runs the same formula. Better still, keep the rates and the target in cells and point at them with $, as in the hard-coded values hunt.
Test the edges
The rows that catch IF mistakes are the ones on the limit and just either side: 19,999, 20,000 and 20,001. If your data doesn't have one, type a test value next to it for a moment.
More IF practice: IF, nested IF, IFS.
FAQ
- Why is my IF formula not working in Excel?
- Check the comparison (> or >=), the order of the two results, numbers written in quotes, and whether the formula has a value for when the test is FALSE.
- Why does my IF formula return FALSE?
- The IF has no third argument, so Excel shows FALSE when the test fails. Add what it should show instead.
- Why does IF ignore my number condition?
- The number is probably in quotes, so Excel compares with text. Remove the quotes, or check that the cell holds a real number, not text.
- How do I write a commission formula in Excel?
- Use IF with the target: =IF(B2>=20000,B2*8%,B2*5%). For several tiers, use IFS or a lookup table.



































































