Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |