Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
Join Date
8th February 2007
Posts
4

I am wanting to set up a formula on a spreadsheet that will take the year end income and determine federal income tax. Example: if value =<50000 then mutiple by 15%, if value is between 50000-75000 then the first 50000 is taxed at 15% and the amount between 50000-75000 is taxed at 25%, if value is between 75000-100000 then first 50000 is taxed at 15%, the next 25,000 is taxed at 25% and the remaining is taxed at 34%. if value is greter than 100000 then same applies as before and all value over 100000 is taxed at 39%.

My brain exploded working on this so if anyone can figure it out that would be great,

Thanks

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Income Tax Spreadsheet

Try this, assuming income is in A1:

= 15% * MIN(A1,50000) + 25% * MAX( 0, MIN(A1-50000, 25000) ) + 34% * MAX( 0, MIN(A1-75000, 25000) ) + 39% * MAX(0, A1-100000)

Excel Video Tutorials / Excel Dashboards Reports

3. Super M‌oderator
Join Date
4th July 2004
Location
Posts
2,371

Here's another way...

=SUMPRODUCT(--(A2>{0,50000,75000,100000}),A2-{0,50000,75000,100000},{0.15,0.1,0.09,0.05})

...where A2 contains the income.

Hope this helps!

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Income Tax Spreadsheet

Use Domenic's -- it's much more elegant and intuitive.

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
8th February 2007
Posts
4

Domenic, this worked great, thanks!!!

: D
Last edited by ttanner; February 8th, 2007 at 23:10.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno