Formula Won't Work - Tax Rates

[Mark McDonough]

Hi all,

Aim: to select from a drop down box one's salary range and then spit out the
marginal tax rate. The rates are as follows:

Australian 2006/2007 Income Tax Rates

$0 - $6,000 Nil
$6,001 - $25,000 15c for each $1 over $6,000
$25,001 - $75,000 $2,850 plus 30c for each $1 over $25,000
$75,001 - $150,000 $17,850 plus 40c for each $1 over $75,000
Over $150,000 $47,850 plus 45c for each $1 over $150,000


Cell B183 is where the user selects their salary range from a drop down box.
The formula I have used (which doesn't work) is given below:

=IF(B183="$0 - $6,000","Nil",
IF(B183="$6,001 - $25,000",15%,
IF(B183="$25,001 - $75,000",30%,
IF(B183="$75,001 - $150,000",40%,
IF(B183="Over $150,000",45%,0)))))

where am I going wrong in this type of formula as I've done a few of them
now and without success.

I also would like a formula such that one types in their salary (actual
dollars - not a range) and the formula is to arrive at the amount of tax due
on a given salary.

 

[Alan]

=IF(F20<=6000,0,IF(F20<=25000,0.15,IF(F20<=75000,0.3,IF(F20<=15000,0.4,IF(F20>15000,0.45,0)))))
Regards,
Alan.

 

[Guest]

Try this:

With a salary value in B183

The incremental rate
=LOOKUP(B183,{0,6000.01,25000.01,75000.01,150000.01},{0,0.15,0.3,0.4,0.45})

The Ta
=SUM((B183/1000>{0,6,25,75,150})*(B183/1000-{0,6,25,75,150})*1000*({0,0.15,0.3,0.4,0.45}))


Also, for fairly exhaustive instructions on working with tiered
calculations, see JE McGimpsey's excellent website:
http://www.mcgimpsey.com/excel/variablerate.html

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Alan]

Change F20 to B183, sorry!

=IF(F20<=6000,0,IF(F20<=25000,0.15,IF(F20<=75000,0.3,IF(F20<=15000,0.4,IF(F20>15000,0.45,0)))))
Regards,
Alan.

 

[Don Guillett]

Try this idea where you just type in the salary.
=SUMPRODUCT(--(B24>{6000,25000,75000,150000}),B24-{6000,25000,75000,150000},{0.15,0.3,0.4,0.45})

 

[Mark McDonough]

Thanks Alan but I find that this formula returns the result of 0.45 every
time. I also changed the amount of 15000 in the formula given to 150000. - a
typo I suspect but this didn't make any difference
The IF statements need to refer to the text salary ranges which are in a
drop down box. Choices in the drop down box are:

"$0 - $6,000"
"$6,001 - $25,000"
"$25,001 - $75,000"
"$75,001 - $150,000"
"Over $150,000"

It is from this data that the results need to spit out the marginal
tax rates.

As an example:

If the salary range chosen is "$75,000 - $150,000"
then the answer should be 30% from the tax rates given below.

 

[Don Guillett]

If all you want is the tax RATE then modify this to suit
=LOOKUP(B28,{"a","b","c"},{1,2,3})

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/variablerate.html

 

[Guest]

Your formula looks fine to me, just make sure that the cell containing the
formula is formatted for percentage or put the percentages in quotes for a
text result, i.e.

=IF(B183="$0 - $6,000","Nil", IF(B183="$6,001 - $25,000","15%",
IF(B183="$25,001 - $75,000","30%", IF(B183="$75,001 - $150,000","40%",
IF(B183="Over $150,000","45%",0)))))

 

[Alan]

The formula returns the tax rate if the exact salary is entered, eg if you
enter $76,500 it returns 40.00%. Sorry about the typo,
Regards,
Alan.