Identifying tax bracket for taxable income 1

[FarzanaSaleem]

I have an Excel workbook with two sheets; Sheet1 and Sheet2.

Sheet1 has three columns:
Column A:
A1 = Total Min Income
A2 = 0
A3 = 80,001
A4 = 150,001

Column B:
B1 = Total Max Income
B2 = 80,000
B3 = 150,000
B4 = 700,000

Column C:
C1 = Tax Amt
C2 = 0
C3 = 2,000
C4 = 5,000

In Sheet2, Total Taxable income is calculated in Cell B1.
What i am trying to achieve is to compare the taxable income with the tax table and display corresponding tax amount in Cell B2.

For example, if B1 = 90,000 then i have to display 2,000 in B2.

One solution that came to my mind is to use IF statement; i.e =IF(AND(B1>=Sheet1!A2,B1<=Sheet1!B2),Sheet1!C2,IF .....), but that will be a very lengthy IF statement as tax table has many ranges.

Please suggest me if there is a more elegant way to achieve this.

Thanks

 

[SkipVought]

Hi,

1. I named the ranges in your table using the heading values (Insert/Name/Create - create names in top row)

2. Here's the formula using thos named ranges...
[tt]
=SUMPRODUCT((Total_Min_Income<B1)*(Total_Max_Income>B1)*Tax_Amt)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[FarzanaSaleem]

Many thanks Skip.

This formula is very concise as compared to the IF statement.

I also read a FAQ faq68-4725 to understand this formula.

What I learned is, this formula has worked as follows in my example (correct me if this formula doesn't work like that):

A B C
1 0 0
1 1 2000
0 1 5000

and then 1*1*2000 = 2000

 

[SkipVought]

You've got it!

Pleased to see that you seek not only a specific solution, but also understanding of the underlying concept!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244