Excel Formula
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 -- Lainyb |
Excel Formula
Try the below
=IF(SUM(COUNTIF(B1,{"51A","51B","51C"})),IF(A1571 5,(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,1 2.8})%+ IF(A140040,(40040-5715)*9.1%,0),"")) If this post helps click Yes --------------- Jacob Skaria "Lainyb" wrote: 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 -- Lainyb |
Excel Formula
Jacob
Thanks very much for your help - it worked a treat. Thanks again -- Lainyb "Jacob Skaria" wrote: Try the below =IF(SUM(COUNTIF(B1,{"51A","51B","51C"})),IF(A1571 5,(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,1 2.8})%+ IF(A140040,(40040-5715)*9.1%,0),"")) If this post helps click Yes --------------- Jacob Skaria "Lainyb" wrote: 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 -- Lainyb |
All times are GMT +1. The time now is 06:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com