Please help with nested if formula

DeniseL

New Member
Joined
Jun 4, 2008
Messages
8
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Oct 18, 2007
Messages
37
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

New Member
Joined
Jun 4, 2008
Messages
8
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

New Member
Joined
Jun 4, 2008
Messages
8

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Jun 4, 2008
Messages
8

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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......
 
Last edited:

Jack Sheet

New Member
Joined
Oct 18, 2007
Messages
37
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.
 
Last edited:

Similar threads

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,572
Members
414,389
Latest member
MarkElla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top