I have a MS Excel IF function Query...?

Can anyone help me to work out the Excel formula used to calculate tax when there are several thresholds involved. i.e. =IF(D3>7475,D3-7475*0.20,"-")+IF(D3>7225,D3-7225*0.09,"-") The above formula works so long as you have just one threshold (7,475 for tax) and (7,225 for National insurance). My problem is that there is a second threshold that comes into play at (35,000) and a third comes into play at (150,000). If you haven't guessed it, this is the UK income tax and national insurance rates that I'm trying to calculate. On a separate formula note, it would also be appreciated if someone could also include the MS Excel formula to use if there is two or more "partners", instead of just a single "sole trader". Sorry, but the formula appears to be truncated in the editor, the second part of the above formula is similar to the first part but uses 0.09 instead of 0.20. Your help is greatly appreciated. Thanks, Wayne

Written by Wayne in Software

Better Answer

Your tax and insurance question has me a bit baffled but I came up with this that might help you.
I created two VLOOKUP tables. One for the Tax and one for the Insurance.

The tables are in M1:N4 and P1:Q4 . These tables have the amounts and related rates for the tax and insurance and then adds them up. You can have as many thresholds with associated rates as is necessary. If the rates and thresholds change, all you have to do is make those changes in the relevant table. The tables start off at 0 values and are arranged in ascending order.

=IF(D3<7225,"",VLOOKUP(D3,M1:N4,2,1)*(D3-7475)+VLOOKUP(D3,P1:Q4,2,1)*(D3-7225))

I hope that I understood your question correctly.

Written by Scrawny