About this lesson
Nested IF and IFS: rating stores in bands in Google Sheets
In Google Sheets, nest IFs for three or more bands, =IF(B2>=0.15,"Strong",IF(B2>=0.08,"OK","Review")), or use IFS, =IFS(B2>=0.15,"Strong",B2>=0.08,"OK",TRUE,"Review"). IFS returns the result of the first test that's TRUE, so put the bands in order and finish with TRUE as a catch-all.
Using Excel? Do this lesson in Excel.
=IF(B2>=0.15,"Strong",IF(B2>=0.08,"OK","Review"))What you'll practise
Rate hardware stores by EBITDA margin with a nested IF, fix an IFS with its bands in the wrong order, then band sales growth with a catch-all.
3 steps · 30 points · the cells outlined in gold below
- nested IF
- IFS
- band order
- TRUE as a catch-all
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Store | EBITDA margin | Sales growth | Margin rating | Growth rating |
| 2 | Parramatta | 16.2% | 4.1% | ||
| 3 | Penrith | 9.4% | -2.3% | ||
| 4 | Wollongong | 7.1% | 1.2% | ||
| 5 | Newcastle | 13.8% | 6.7% | ||
| 6 | Gosford | 15.3% | 5.4% | ||
| 7 | Dubbo | 8% | -5.1% | ||
| 8 | Albury | -1.8% | -3.4% | ||
| 9 | Orange | 11.2% | 9.3% | ||
| 10 | Bathurst | 5.9% | -0.8% | ||
| 11 | |||||
| 12 | Draft (Parramatta) | OK |