Please help with nested if formula

[DeniseL]

I have a nested if formula which is supposed to calculate stamp duty on a sliding scale and I just cannot get it to work (must be a super dumb day).

Cells have different numbers based on other calculations but cell B8 = 67,320 and the answer should be 1068.1

The formula is
=IF(BA8<14000,BA8*0.0125,IF(BA8>14000<30000,(BA8-14000)*0.015+175,IF(BA8>30000<80000,(BA8-30000)*0.0175+415,IF(BA8>80000<300000,(BA8-80000)*0.035+1290,IF(BA8>300000<1000000,(BA8-300000)*0.045+8990,IF(BA8>1000000,(BA8-1000000)*0.055+40490))))))

Any help with syntax and/or other errors I have made would be much appreciated.

Thanks, DeniseL

 

[DonkeyOte]

As a heads up -- there are a couple of issues here

An IF will work along the lines that each test will be executed until such time as test is TRUE at which point IF will stop, eg:

IF(A1=1,1,IF(A1=2,2,IF(A1=3,3,4)))

So for A1=2 I do not need to test that A1 <> 1 as if it did the IF would already have stopped and returned 1 as it was the first test. Make sense ?

Thus it follows a lot of your "tests" aren't required as they've already been checked...

If you did need to do a "double check" (bad term) ie A1 <> 1 and A1 > 2 then you need to use the following syntax

=IF(AND(A1<>1,A1>2),result if true, result if false)

and if it were an OR test

=IF(OR(A1=1,A1=3),result if true, result if false)

The above should help you refine your formula.

 

[Jack Sheet]

Slightly different approach, but you might find this easier to follow:

=MAX(0,BA8-1000000)*0.01+MAX(0,BA8-80000)*0.0275+MAX(0,BA8-30000)*0.0025+MAX(0,BA8-14000)*0.0025+BA8*0.0125

 

[DeniseL]

Thanks lasw10. Forgive me for being a bit slow but I don't really understand how, if I apply what you have said, the formula would determine which range (eg, >14000<30000 or >30000<80000) BA8 would fall within and then apply the stamp duty rate applicable to that particular range.
Thanks again, DeniseL

 

[DeniseL]

Thanks Jack Sheet. Another dumb question. Does using your 'max' formula look at the range (eg, >14000<30000) or does it look at everything <30000?
Thanks again, Denise L

 

[barry houdini]

I think Jack's suggestion misses one of the levels [300000]. Here's a similar approach

=BA8*5.5%-MIN(1000000,BA8)*1%-MIN(300000,BA8)*1%-MIN(80000,BA8)*1.75%-MIN(30000,BA8)*0.25%-MIN(14000,BA8)*0.25%

...or here's another way to get the same result

=SUMPRODUCT(--(BA8>{0,14000,30000,80000,300000,1000000}),BA8-{0,14000,30000,80000,300000,1000000},{1.25,0.25,0.25,1.75,1,1})/100

This follows the method outlined here

 

[DeniseL]

Many thanks Barry Houdini. How would I include in your formula the flat rate amount applicable to each range?
Thanks and regards, DeniseL

 

[barry houdini]

How would I include in your formula the flat rate amount applicable to each range?

You don't need to, that's already accounted for. For example, in your formula if BA8 = 20000 you are calculating

6000*0.015+175 = 265

Both formulas I suggested will give the same result

Edit: following lasw10's suggested approach your original formula would become

=IF(BA8<14000,BA8*0.0125,IF(BA8<30000,(BA8-14000)*0.015+175,IF(BA8<80000,(BA8-30000)*0.0175+415,IF(BA8<300000,(BA8-80000)*0.035+1290,IF(BA8<1000000,(BA8-300000)*0.045+8990,(BA8-1000000)*0.055+40490)))))

Check the results, all 3 should give the same......

 

[DeniseL]

Barry Houdina - a living legend! Thank you so much.
DeniseL

 

[Jack Sheet]

Good catch Barry, I missed a range. So it should be:

=MAX(0,BA8-1000000)*0.01+MAX(0,BA8-300000)*0.01+MAX(0,BA8-80000)*0.0175+MAX(0,BA8-30000)*0.0025+MAX(0,BA8-14000)*0.0025+BA8*0.0125

DeniseL

My formula looks at each range from 0 up within each MAX() function.

To summarise, the rate table appears to be this:

Tranche--------Marginal rate-------True rate on tranche
14000---------- 0.0125 ---------- 0.0125
30000---------- 0.0025 ----------- 0.015
80000---------- 0.0025 ----------- 0.0175
300000--------- 0.0175 ----------- 0.035
1000000-------- 0.01 ------------- 0.045
Excess--------- 0.01 ------------- 0.055

My solution charges 0.01 on anything over 1000000
Then it adds on a further charge of 0.01 on anything over 300000, INCLUDING the slice over 1000000. So, up to that point it has charged 0.02 on anything over 1000000 (0.01 + 0.01), but only 0.01 on the tranche between 300000 and 1000000.
Then it adds on a further charge of 0.0175 on anything over 80000, INCLUDING the higher two slices, and so on.
By the end, the first 14000 has been charged on just 0.0125, and anything over 1000000 has been charged on 0.055 (being the sum of 0.0125+0.0025+0.0025 etc).

Incidentally, if these rates are likely to change from one year to the next, then you may find it more useful to put the rates into a table and then use the VLOOKUP function or similar to access them in the formula. That would make maintenance a bit easier, and would also make the workbook easier to understand should someone else have to pick it up and run with it.