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))))))))))))))```
Oct 15, 2022 in Others 470 views

## 1 answer to this question.

You can replace it with a set of vlookups:

`=((D8-VLOOKUP(D8,H8:K19,1,1))*VLOOKUP(D8,H8:K19,3,1))+VLOOKUP(D8,H8:K19,4,1)`
answered Oct 15, 2022 by
• 63,720 points

## How to compare two excel sheets

How to compare two excel sheets , ...READ MORE

## Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

## csv to excel conversion

So Basically u have to follow few ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

## In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

## Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE