Home › Practice questions › IFS › Question 7

Excel IFS Practice Question: Find the amount (quantity times price) with a discount on the whole amount:…

By Rinkesh Shah, founder of Cellgrids.

The question

Find the amount (quantity times price) with a discount on the whole amount: 20% off for 100 or more units, 10% off for 50 or more, otherwise none. Put it in C2 to C4.

The data

	A	B	C
1	Qty	Price	Amount
2	120	10	?
3	60	20	?
4	10	50	?

The yellow cells are where your answer goes.

Show answer

Expected results:

C2 = 960
C3 = 1080
C4 = 500

How it works

Explanation: IFS returns the multiplier (0.8, 0.9 or 1) and it is multiplied with quantity times price.
Syntax: =A2*B2*IFS(A2>=100,0.8,A2>=50,0.9,TRUE,1)
120 x 10 x 0.8 = 960. 60 x 20 x 0.9 = 1080. 10 x 50 x 1 = 500.

Try it yourself in the live sheet

Type your formula in the highlighted cell and get instant feedback. Free, no Excel install needed.

Try this question in the live sheet →

IFS syntax

=IFS(logical_test1, value1, [logical_test2, value2], ...)

Checks multiple conditions in order and returns the value for the first one that's true. IFS is used to build decision rules directly into a spreadsheet instead of manually judging each row. Full IFS guide.