What's wrong with this formula - NESTED IF STATEMENTS

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark McDonough - 03 Jan 2007 10:44 GMT

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

Reply to this Message

Bob Phillips - 03 Jan 2007 11:57 GMT

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> I'm having a problem with an if statement (given below) for varying tax
> rates based on given amounts.
[quoted text clipped - 31 lines]
> ----= East and West-Coast Server Farms - Total Privacy via Encryption
> =----

Reply to this Message

Mark McDonough - 03 Jan 2007 12:46 GMT

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

> =IF(C1<=1300,20,0)+
> IF(C1<=10000,C1*1.5%,IF(C1<=30000,150+(C1-10000)*2%,
[quoted text clipped - 36 lines]
>> ----= East and West-Coast Server Farms - Total Privacy via Encryption
>> =----

Reply to this Message

Bob Phillips - 03 Jan 2007 13:19 GMT

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Thanks heaps Bob, this works well. Sorry to trouble you for another one:
>
[quoted text clipped - 67 lines]
> ----= East and West-Coast Server Farms - Total Privacy via Encryption
> =----

Reply to this Message

Harlan Grove - 03 Jan 2007 20:22 GMT

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)

Reply to this Message