You will encounter slab billings in two very common documents – One is Electricity / Utility and another one is Income Tax. If you see your electricity bills, you will notice following type of entries (values are for illustration purposes only, please do not attach any meaning to them)
0 – 50 Units – $1.5 per Unit
51-100 Units – $ 2.0 per Unit
101-500 Units – $3.5 Per Unit
501 – 2000 Units – $6 Per Unit
2000 Unit Onwards – $9 Per Unit
It means that your first 50 units will be charged at $1.5 per unit, next 50 will be charged at $2.0 per unit and next 400 units will be charged at $3.5 per unit, next 1500 units will charged at $6 per unit and any unit after 2000 will be charged at $ 9 per unit.
Hence, if your bill is 37 units, your charges will be = 37*1.5 = $55.5
Hence, if you bill is 87 units, you charges will be = 50*1.5+(87-50)*2 = $149
Hence, if your bill is 415 units, your charges will be = 50*1.5+50*2+(415-100)*3.5 = $1277.5
Hence, if your bill is 780 units, your charges will be = 50*1.5+50*2+400*3.5+(780-500)*6 = $3255
Hence, if your bill is 2670 units, your charges will be = 50*1.5+50*2+400*3.5+1500*6+(2670-2000)*9 = $16605
Same type of distribution, you will notice in Income Tax also. Consider the below slabs.
0 – $10000 – Nil
$10001 – $50000 – 10%
$50001 – $100000 – 20%
$100001 -$500000 – 30%
$500001 and above – 35%
Hence, if your income is $8000, your income tax will be 0.
Hence, if your income is $40000, your income tax will be =(40000-10000)*10% = $3000
Hence, if your income is $90000, your income tax will be =40000*10%+(90000-50000)*20% = $12000
Hence, if your income is $210000, your income tax will be =40000*10%+50000*20%+(210000-100001)*30% = $47000
Hence, if your income is $850000, your income tax will be =40000*10%+50000*20%+400000*30%+(850000-500000)*35% = $256500
Download workbook related to this article from Article 31 – Slab Billing
Now, how to calculate this in Excel through a single formula. Here, SUMPRODUCT comes to your rescue. The only per-requisite is that you need to set up your data appropriately for this purpose as given below.
Then the formula for slab billing is simply following (for older version of Excel, you may be required to enter it as Array formula i.e. by pressing CTRL+SHIFT+ENTER)
=SUMPRODUCT((F2>A2:A6)*(F2-A2:A6)*(C2:C6-IFERROR(–C1:C5,0)))
Below is the example for Income Tax
Then the formula for Income Tax is simply following (for older version of Excel, you may be required to enter it as Array formula i.e. by pressing CTRL+SHIFT+ENTER)
=SUMPRODUCT((O2>J2:J6)*(O2-J2:J6)*(L2:L6-IFERROR(–L1:L5,0)))