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.




Try this:

With a salary value in B183

The incremental rate

The Ta

Also, for fairly exhaustive instructions on working with tiered
calculations, see JE McGimpsey's excellent website:

Is that something you can work with?

XL2002, WinXP

Don Guillett

Try this idea where you just type in the salary.

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


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


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,

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question