IFS
Work down the bands in order.
Work down a list of bands and stop at the first that holds
The answer paired with the first test that comes out TRUE
Syntax
=IFS(test1, answer1, [test2], [answer2], ...)
| Argument | Required | What goes here |
|---|---|---|
test1 | Required | The narrowest band, checked first. |
answer1 | Required | What to show when that first test holds. |
test2 | Optional | The narrowest band, checked next. |
answer2 | Optional | What to show when that next test holds. |
How to use IFS
IFS takes tests and answers in pairs. It checks them in the order you wrote them and returns the answer beside the first test that holds, then stops.
Because it stops at the first match, the order is the whole design. An ageing band has to run from the widest to the narrowest, or every row falls into the first bucket.
There is no otherwise slot. The way to write one is a final pair whose test is TRUE, which always holds and so catches everything left.
Without that catch-all, a row matching none of the tests gives an error rather than a blank.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=IFS(D7>90,"90+",D7>60,"61-90",D7>30,"31-60",TRUE,"Current")Ninety-one days. The first test holds, so nothing after it is looked at. | 90+ |
=IFS(D6>90,"90+",D6>60,"61-90",D6>30,"31-60",TRUE,"Current")Thirty-eight days falls past the first two tests and lands on the third. | 31-60 |
=IFS(D2>90,"90+",D2>60,"61-90",D2>30,"31-60",TRUE,"Current")Twelve days matches no band, so the TRUE catch-all answers. | Current |
=IFS(D7>30,"31-60",D7>60,"61-90",D7>90,"90+")The same row with the bands in the wrong order. Ninety-one days is reported as 31-60, and nothing about the formula looks broken. | 31-60 |
Notes
- Every test needs an answer beside it. An odd number of arguments is an error.
- A row that matches no test returns an error. Finish with TRUE and the answer you want for everything else.
Related
A reference tells you what it does. A unit makes you use it.