What's wrong with this formula - NESTED IF STATEMENTS

[Mark McDonough]

I'm having a problem with an if statement (given below) for varying tax
rates based on given amounts.

The purpose is to determine the stamp duty payable for a purchase of a given
value. The rates are:

$1 - $1300 tax due is $20
$1301 - $10,000 tax due is 1.5%
$10,001 - $30,000 tax due is $150+2% over $10,000
$30,001 - $75,000 tax due is $550+2.5% over $30,000
$75,001 - $150,000 tax due is $1675+3% over $75,000
$150,001 - $225,000 tax due is $3925+3.5% over $150,000
$225,001 and above, tax due is $6,550+4% over $225,000


Cell C1 is where the taxable amount is entered:

=IF(C1<=1300,20,
IF(C1<=10000,0.015*C1),
IF(C1<=30000,150+0.02*(C1-10000),
IF(C1<=75000,550+0.025*(C1-30000),
IF(C1<=150000,1675+0.03*(C1-75000),
IF(C1<=225000,3925+0.035*(C1-150000),
IF(C1>225000,6550+0.04*(C1-225000)))))))


Does anyone know where I'm going wrong?

Cheers
Mark

 

[Bob Phillips]

=IF(C1<=1300,20,0)+
IF(C1<=10000,C1*1.5%,IF(C1<=30000,150+(C1-10000)*2%,
IF(C1<=75000,550+(C1-30000*2.5%),IF(C1<=150000,1675+(C1-75000)*3%,
IF(C1<=225000,3925+(C1-150000)*3.5%,IF(C1>225000,6550+(C1-225000)*4%,0))))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Mark McDonough]

Thanks heaps Bob, this works well. Sorry to trouble you for another one:

1% up to $12,000
$12,001 - $30,000 $120 + 2% of dutiable value over $12,000
$30,001 - $50,000 $480 + 3% of dutiable value over $30,000
$50,001 - $100,000 $1080 + 3.5% of dutiable value over $50,000
$100,001 - $200,000 $2830 + 4% of dutiable value over $100,000
$200,001 - $250,000 $6830 + 4.25% of dutiable value over $200,000
$250,001 - $300,000 $8955 + 4.75% of dutiable value over $250,000
$300,001 - $500,000 $11,330 + 5% of dutiable value over $300,000
$21,330 + 5.5% of dutiable value over $500,000


My formula (which doesn't work) is below:

=IF(C1<=12000,0.01*C1,
IF(C1<=30000,120+0.02*(C1-12000),
IF(C1<=50000,480+0.03*(C1-30000),
IF(C1<=100000,1080+0.035*(C1-50000),
IF(C1<=200000,2830+0.04*(C1-100000),
IF(C1<=250000,6830+0.0425*(C1-200000),
IF(C1<=300000,8955+0.475*(C1-250000),
IF(C1<=500000,11330+0.05*(C1-300000),
IF(C1>500000,21330+0.055*(C1-500000))))))))))

 

[Bob Phillips]

=IF(C1<=1200,C1*1%,0)+
IF(C1<=30000,120+(C1-12000)*2%,IF(C1<=50000,480+(C1-30000)*3%,
IF(C1<=100000,1080+(C1-50000*3.5%),IF(C1<=200000,2830+(C1-100000)*4%,
IF(C1<=250000,6830+(C1-200000)*4.25%,IF(C1>300000,8955+(C1-250000)*4.75%
IF(C1<=50000011330+(C1-300000)*5%,(C1-500000)*5.5%)))))))


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Harlan Grove]

Bob Phillips wrote...

=IF(C1<=1200,C1*1%,0)+
IF(C1<=30000,120+(C1-12000)*2%,IF(C1<=50000,480+(C1-30000)*3%,
IF(C1<=100000,1080+(C1-50000*3.5%),IF(C1<=200000,2830+(C1-100000)*4%,
IF(C1<=250000,6830+(C1-200000)*4.25%,IF(C1>300000,8955+(C1-250000)*4.75%
IF(C1<=50000011330+(C1-300000)*5%,(C1-500000)*5.5%)))))))

....

Or put the schedule into a 2-column table like the following in X21:Y29

0 0.01
12000 0.02
30000 0.03
50000 0.035
100000 0.04
200000 0.0425
250000 0.0475
300000 0.05
500000 0.055

and use an array formula like

=SUM(IF(x>$X$22:$X$29,($X$22:$X$29-$X$21:$X$28)*$Y$21:$Y$28))
+(x-LOOKUP(x,$X$21:$X$29))*LOOKUP(x,$X$21:$Y$29))

or put the schedule into a 3-column table like the following in X21:Z29

0 12000 0.01
12000 30000 0.02
30000 50000 0.03
50000 100000 0.035
100000 200000 0.04
200000 250000 0.0425
250000 300000 0.0475
300000 500000 0.05
500000 1.00E+12 0.055

and use an array formula like

=SUM(IF(x>$X$21:$X$29,(IF(x<$Y$21:$Y$29,x,$Y$21:$Y$29)-$X$21:$X$29)*$Z$21:$Z$29))

This sort of thing is MUCH SIMPLER if you use tables. If you don't want
to use tables, at least define the following names.

LoBnd:
={0;12000;30000;50000;100000;200000;250000;300000;500000}

UpBnd:
={12000;30000;50000;100000;200000;250000;300000;500000;1000000000000}

Rates:
={0.01;0.02;0.03;0.035;0.04;0.0425;0.0475;0.05;0.055}

and use an array formula like

=SUM(IF(x>LoBnd,(IF(x<UpBnd,x,UpBnd)-LoBnd)*Rates))

or a nonarray formula like

=SUMPRODUCT((x>LoBnd)*((x<=UpBnd)*x+(x>UpBnd)*UpBnd-LoBnd),Rates)