Home › Practice questions › IF › Question 13

Excel IF Practice Question: Income is in B2:B6. Tax is 0 up to 250000; 5% on the part above 250000 up to…

By Rinkesh Shah, founder of Cellgrids.

The question

Income is in B2:B6. Tax is 0 up to 250000; 5% on the part above 250000 up to 500000; and 12500 plus 20% on the part above 500000. Show the tax in D2:D6.

The data

	A	B	D
1	Employee	Income	Tax
2	E-101	220000	?
3	E-102	350000	?
4	E-103	500000	?
5	E-104	640000	?
6	E-105	900000	?

The yellow cells are where your answer goes.

Show answer

Expected results:

D2 = 0
D3 = 5000
D4 = 12500
D5 = 40500
D6 = 92500

How it works

Explanation: Test the lowest slab first, then the next. Each IF only runs when the earlier test was false. The top slab adds the fixed tax of the lower slabs.
Syntax: =IF(B2<=250000,0,IF(B2<=500000,(B2-250000)*0.05,12500+(B2-500000)*0.2))
350000 gives 5000. 500000 gives 12500. 640000 gives 12500 + 28000 = 40500.

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 →

IF syntax

=IF(logical_test, value_if_true, value_if_false)

Returns one value if a condition is true, another if false. Flagging an employee as Pass or Fail based on a score cutoff. Full IF guide.