Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
IFS
70 isn't over 100, but it is over 50, so the sheet shows Medium. TRUE at the end catches everything else, like an "otherwise". Older sheets do the same with an IF inside an IF, called a nested IF: =IF(F2>100,"Large",IF(F2>50,"Medium","Small")).
Try this lesson free →Example
IF gives two answers. IFS checks tests in order and shows the answer for the first one that's true:
=IFS(F2>100,"Large",F2>50,"Medium",TRUE,"Small")| Order | Day | Store | Product | Qty | Revenue | Label |
|---|---|---|---|---|---|---|
| O101 | Mon | Lagos | Earbuds | 2 | 70 | |
| O102 | Mon | Abuja | Charger | 1 | 15 | |
| O103 | Tue | Lagos | Phone case | 3 | 24 | |
| O104 | Wed | Lagos | Earbuds | 1 | 35 | |
| O105 | Wed | Abuja | Earbuds | 4 | 140 | |
| O106 | Thu | Lagos | Charger | 2 | 30 | |
| O107 | Fri | Abuja | Phone case | 5 | 40 | |
| O108 | Fri | Lagos | Earbuds | 3 | 105 |
Key points
F2>100,"Large"A test, then what to show if it's true.
F2>50,"Medium"The next pair is only checked if the test before it was false.
TRUE,"Small"TRUE at the end means "everything else".
Put together: =IFS(F2>100,"Large",F2>50,"Medium",TRUE,"Small"). Order matters: put the biggest test first.
Practice
1. In G3, show Large if the revenue is over 100, Medium if it's over 50, otherwise Small.
=IFS(F3>100,"Large",F3>50,"Medium",TRUE,"Small")2. In G4, show Bulk if the quantity is 3 or more, Pair if it's exactly 2, otherwise Single.
=IFS(E4>=3,"Bulk",E4=2,"Pair",TRUE,"Single")3. In G6, show Large if the revenue is over 100, Medium if it's over 50, otherwise Small.
=IFS(F6>100,"Large",F6>50,"Medium",TRUE,"Small")4. In G7, show Bulk if the quantity is 3 or more, Pair if it's exactly 2, otherwise Single.
=IFS(E7>=3,"Bulk",E7=2,"Pair",TRUE,"Single")5. In G9, show Local if the store is Lagos, Road if it's Abuja, otherwise Air.
=IFS(C9="Lagos","Local",C9="Abuja","Road",TRUE,"Air")Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.
Try this lesson free →