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

 

[Padawan]

Barry

Would you mind giving a quick explaination as to why there are brackets {} in the middle of your sumproduct formula? I've used them for array formulas, but I don't think I've ever seen them in the middle of a formula.

Thank you.

 

[barry houdini]

This is an "array constant"

{1.25,0.25,0.25,1.75,1,1}

In a formula like SUMPRODUCT you can use either ranges, like A1:F1, or arrays like the above [or "calculated arrays" like the other 2 in the formula]. If A1:F1 was to contain the same six values then you could use either range or array interchangeably and get the same results.

Note that some formulas, like SUMIF and COUNTIF, only accept ranges, not arrays, as the criteria or sum range.

 

[Padawan]

Barry

You never cease to amaze me. I don't believe I have ever heard of an array constant. You have, once again, given me new directions and some more reading to do as I continue to learn.

I'm even starting to understand this very powerful sumproduct formula.

Thanks again.