# Formula Won't Work - Tax Rates

M

#### 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.

A

#### 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.

G

#### 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

A

#### Alan

Change F20 to B183, sorry!
Alan said:
=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.

D

#### 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})

M

#### 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.

D

#### Don Guillett

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

J

G

#### 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)))))

A

#### 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.