Logic › IFS43 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeIFS

IFS: tiered shipping by weight

Fajar sells bottled sambal from Solo and ships through a local courier. Rates depend on parcel weight: up to 1 kg Rp12,000, over 1 up to 3 kg Rp20,000, over 3 up to 5 kg Rp28,000, and over 5 kg Rp40,000.

Task

Fill C2:C7 with the shipping fee for the parcel weight in column B. Use IFS.

Cells to fillC2:C7

Required functionsIFS

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
IFS takes condition/result pairs. The first condition that is TRUE decides the result, and the rest are ignored.
Hint 2
Go from the lightest weight up using <=: check B2<=1 first, then B2<=3, then B2<=5.
Hint 3
For "anything else", use TRUE as the last condition, for example …, TRUE, 40000).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

C2:C7

=IFS(B2<=1,12000,B2<=3,20000,B2<=5,28000,TRUE,40000)

Write it in C2, then fill down to C7.

Explanation

=IFS(B2<=1,12000,B2<=3,20000,B2<=5,28000,TRUE,40000) reads the pairs from the left: a 2.5 kg parcel fails B2<=1 and then matches B2<=3, so the fee is 20,000. Because the order runs from small to large, "over 1 kg" never needs to be written. IFS has no "if everything is false" argument; without the TRUE, 40000 pair, the 7 kg parcel returns #N/A. Tip: rates that change often are better kept in a table and looked up with an approximate-match XLOOKUP.

Starting worksheet: Sheet1
Row	A	B	C
1	Parcel	Weight (kg)	Shipping fee
2	PKT-01	0.8
3	PKT-02	1.0
4	PKT-03	2.5
5	PKT-04	3.0
6	PKT-05	4.2
7	PKT-06	7.0
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result