Help with calculating tax percentages

[TerryJR]

I've spent hours looking for the answer to this and can't find it. I'
working on a home budget spreadsheet and it includes a tax table that
can update.

I've got a couple of the tax brackets working but I can't figure out th
middle one.

$18,201 - $37,000 = 19c for each $1 over $18,200
$37,001 - $80,000 = 32.5c for each $1 over $37,000
$80,001 - $180,000 = 37c for each $1 over $80,000

What if then statement do I use to say, If "cell x" is greater tha
37001, but less than 80000, then multiply "the unknown number inbetween
by 32.5%.

Perhaps I'm using the wrong formula? Suggestions anyone? I appreciat
the help

 

[Kevin@Radstock]

Hi TerryJr

Couple of ways. Assuming the cell x value is in A1.

1: LOOKUP:
=A1*LOOKUP(A1,{0,18201,37001,80001},{0,0.19,0.325,0.37})

2: VLOOKUP:
If you place a small table somewhere in the sheet. Say D1:E4
.......0 0.0%
18201 19.0%
37001 32.5%
80001 37.0%

=A1*VLOOKUP(A1,D1:E4,2,1)

http://www.contextures.com/xlFunctions02.html

 

[TerryJR]

thx Kevin

I've never heard of this type of formula at all, but I'll have a play.


cheer