In Excel, I'm creating a tax calculator. I've used the if and command for this, but only the limited slabs' results are displayed. Both the tax table and my formula are posted. I need your help to make the formula shorter.
Tax Slabs Tax Rate Additional Tax
- 400,000 0% -
400,000 500,000 2.00% -
500,000 750,000 5.00% 2,000
750,000 1,400,000 10.00% 14,500
1,400,000 1,500,000 12.50% 79,500
1,500,000 1,800,000 15.00% 92,000
1,800,000 2,500,000 17.50% 137,000
2,500,000 3,000,000 20.00% 259,500
3,000,000 3,500,000 22.50% 359,500
3,500,000 4,000,000 25.00% 472,000
4,000,000 7,000,000 27.50% 597,000
7,000,000 30.00% 1,422,000
Tax slabs are in columns H and I. Tax Rates slabs are in column J and the Additional tax is in column K.
Here is the formula
=IF(AND($D8>$H$8,$D8<=$I$8),(($D8-$H$8)*$J$8+$K$8),IF(AND($D8>$H$9,$D8<=$I$9),(($D8-
$H$9)*$J$9+$K$9),IF(AND($D8>$H$10,$D8<=$I$10),(($D8-
$H$10)*$J$10+$K$10),IF(AND($D8>$H$11,$D8<=$I$11),(($D8-
$H$11)*$J$11+$K$11),IF(AND($D8>$H$12,$D8<=$I$12),(($D8-
$H$12)*$J$12+$K$12),IF(AND($D8>$H$13,$D8<=$I$13),(($D8-
$H$13)*$J$13+$K$13),IF(AND($D8>$H$14,$D8<=$I$14),(($D8-
$H$14)*$J$14+$K$14),IF(AND($D8>$H$15,$D8<=$I$15),(($D8-
$H$15)*$J$15+$K$15,IF(AND($D8>$H$16,$D8<=$I$16),(($D8-
$H$16)*$J$16+$K$16,IF(AND($D8>$H$17,$D8<=$I$17),(($D8-
$H$17)*$J$17+$K$17),IF(AND($D8>$H$18,$D8<=$I$18),(($D8-
$H$18)*$J$18+$K$18),IF(AND($D8>$H$19),(($D8*$J$19)+$K$19),0))))))))))))))
