<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
---|

**Excel: Calculate Sliding Scale Tax/Commission. Calculate Bracket Tax/Commission**

**See Also: Calculate Sliding Scale Tax Custom Excel Function VBA.Download Working Examples**

Calculating tax, or commission that is based on a sliding scale, or by bracket, can be complicated. The formula below is one that can be used using Excel's built in function/formulas. That is, the IF function/formula and the SUM function/formula. The formula that can be used is;

=IF(A5>Level4Tax,SUM((A5-Level4Tax *Level4TaxRate,Level3TaxAmount*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate),IF(A5>Level3Tax,SUM((A5-Level3Tax)*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate),IF(A5>Level2Tax,SUM((A5-Level2Tax)*Level2TaxRate,Level1TaxAmount*Level1TaxRate),SUM((A5-Level1Tax)*Level1TaxRate)),0)))

I have color coded the formula for easier reading. As you can see, this formula uses **Named Ranges**for easier reading and modification. *Anamed range can be created by selecting the cell, then typing the name wanted in the Name Box (left of formula bar) and pushing Enter.*

To make this easier to read, I have placed the cell names next to their named cell. In the formula above, only the grey cells are being used. The last column (**Amount of Tax Payable on**) is the result of subtracting the Level*Tax (one row down) from the Level*Tax on the same row. For example, $13,000.00 (**Level1TaxAmount**) is derived by subtracting **Level2Tax **(25000) from **Level1Tax** (12000). That is:

**=Level2Tax-Level1Tax**

If you prefer, these key numbers, can become **Named Constants** as opposed toNamed Ranges . For example, to create the Named Constant: **Level1Tax **you would go to **Insert**>**Name**>**Define** and type: **Level1Tax** in the * Names in workbook:* box, then:

**UsingExcel Vlookup Formula**

There is another way, which some may prefer where the **VLOOKUP **function/formula is used. This method relies on some "*Quick deductions*" being pre calculated and placed at the end of the white & grey table shown above.

**=A3*VLOOKUP(A3,$B$14:$G$18,3)-VLOOKUP(A3,$B$14:$G$18,6)**

This is best seen by **Downloading Working Example Thanks to Albert Tsang for this excellent method.**

We can go one step further toward simplifying the calculation by using **Named Formulas **for each tax level calculation. After doing this, we can then use;

=IF(A1>Level4Tax,Level4TaxCalc,IF(A1>Level3Tax,Level3TaxCalc,IF(A1>Level2Tax,Level2TaxCalc,IF(A1>Level1Tax,Level1TaxCalc,0))))

Here are the steps to achieve this. BTW, this method can also be used to overcome the **7 nested IF Function limitation **.

1) Create Named Ranges, or Named Constants that will hold the figures needed. See screen shot above.

2) Place you Gross pays in cell **A1** down.

3) Select cell **B1** and go to **Insert**>**Name**>**Define**.

4) In the **Names in workbook:** box type: **Level1TaxCalc** Then, in the**Refers to:** box type: **=SUM((A1-Level1Tax)*Level1TaxRate)** Then click**Add**. ***Note how we have referred to cell A1. This now makes the Named Formula (Level1TaxCalc) always look on the same row in the immediate column to the left for the gross pay*.

5) Repeat step

**Level2TaxCalc**=SUM((A1-Level2Tax)*Level2TaxRate,Level1TaxAmount*Level1TaxRate)

=SUM((A1-Level4Tax)*Level4TaxRate,Level3TaxAmount*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate)

**See Also: Calculate Sliding Scale Tax Custom Excel Function VBA.Download Working Examples**

**Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money**

**Special! Free Choice** of Complete Excel Training Course **OR **Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 **gets you BOTH! Purchases MUST be made via this site**. Send payment proof to [email protected] 31 days after purchase date.

**Instant Download and Money Back Guarantee on Most Software**

**Excel Trader Package**Technical Analysis in Excel With $139.00 of **FREE software!**

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

**Some of our more popular products are below...**