Excel Formula

[Lainyb]

Could someone please help with the following formula as I keep coming up with
too many arguments.

Column 1 - Salaries Column 2 - NI code Column 3 - Result
£ 1,535.25 51A
£ 25,125.00 51A
£ 45,000.00 51D
£ 2,900.00 51D
£ 56,475.00 51A
£ 15,225.00 51C

I want to use the above columns to calculate the NI for each person using
the following forumulas:

If Column 2 is 51A,51B or 51C then lookup column 1 and calculate - if column
1 =< 5715 then result is 0, if column 1 > 5715 then (column 1 - 5715)*12.8%

If Column 2 is 51D or 51E then lookup column 1 and calculate - if column 1
=< 5715 then result is 0, if column 1 > 40040 then sum ((column 1 -
40040)*12.8%+(40040-5715)*9.1%) or if over 5715 and under 40040 then
calculate - sum(column 1-5715)*9.1%

Any help with this would be great, I have been tearing my hair out.

Thanks

 

[Jacob Skaria]

Try the below

=IF(SUM(COUNTIF(B1,{"51A","51B","51C"})),IF(A1>5715,(A1-5715)*12.8%,0),
IF(SUM(COUNTIF(B1,{"51D","51E"})),(A1-LOOKUP(A1,{0,5716,40041},
{0,5715,40040}))*LOOKUP(A1,{0,5716,40041},{0,9.1,12.8})%+
IF(A1>40040,(40040-5715)*9.1%,0),""))

If this post helps click Yes

 

[Lainyb]

Jacob

Thanks very much for your help - it worked a treat.

Thanks again