IF with multiple arguments

krokodilizm

New Member
Guys can someone compare the below two and tell me where is the syntax error in 2nd formula? Formula 1 works fine (I copied it from a thread and changed inputs). In formula 2 I want to expand formula 1.

Formula 1 =IF(E5<=1625,(E5*0),IF(E5<=7000/3,(0+((E5-1625-E5*7.8%+$B$10/12+$B$11/12)*0.2)),(425/3+(E5-7000/3-E5*7.8%+$B$10/12+$B$11/12)*0.25)))

Formula 2 =IF(E9<=1625,(E9*0),IF(E9<=7000/3,(0+((E9-1625-E9*7.8%+$B$10/12+$B$11/12)*0.2)),(425/3+(E9-7000/3-E9*7.8%+$B$10/12+$B$11/12)*0.25))),(425.25+(E9-36300-E9*7.8%+$B$10/12+$B$11/12)*0.30)))),(4090/3+(E9-5000-E9*7.8%+$B$10/12+$B$11/12)*0.35)))))

The inputs are for tax calculation using different bands. First one calculates 2 bands, but I want to incorporate 4 bands.
 

AlanY

Well-known Member
what are the conditions for

(425.25+(E9-36300-E9*7.8%+$B$10/12+$B$11/12)*0.30))))

and

(4090/3+(E9-5000-E9*7.8%+$B$10/12+$B$11/12)*0.35))))) ?
 

Joe4

MrExcel MVP, Junior Admin
Welcome to the Board!

However many bands you have, you will have one less IF statement.
Let's say that your bands are:
<1000
<2000
<3000
>=3000

Then the structure of your nested IF statement would be:
Code:
=IF(E5<1000,calculation1,IF(E5<2000,calculation2,IF(E5<3000,calculation3,calculation4)))
 

krokodilizm

New Member
So here I added Joey's and Alan's ideas, but still Excel output = too many arguments.

=IF(E9<=1625,(E9*0),IF(E9<=7000/3,(0+((E9-1625-E9*7.8%+$B$10/12+$B$11/12)*0.2)),(425/3+(E9-7000/3-E9*7.8%+$B$10/12+$B$11/12)*0.25)),IF(E9<=3025,(425/3+((E9-7000/3-E9*7.8%+$B$10/12+$B$11/12)*0.25)),(3775/12+(E9-3025-E9*7.8%+$B$10/12+$B$11/12)*0.30)),IF(E9<=5000,(5475/12+((E9-3025-E9*7.8%+$B$10/12+$B$11/12)*0.30)),(10885/12+(E9-5000-E9*7.8%+$B$10/12+$B$11/12)*0.35)))

Here are the bands I am using*

Band%Accumulated
tax
0-1625-nil
1626-7000/320425/3
7001/3-3025253775/12
3026-50003010885/12
over 500035

<tbody>
</tbody>

* The data is for Cyprus income tax and I divided everything by 12 to do monthly analysis
 

Joe4

MrExcel MVP, Junior Admin
Joey?!?!? My Mom doesn't even get to call me Joey!

It is important to understand how to nest IF statements. The structure of the IF function if:
=IF(condition, what to do if true, what to do if false)

So each IF statement can only have 3 arguments. When nesting, the third argument (false portion) is always your next IF statement, until you get to the very last one. In your second IF statement in your formula, you have 4 arguments. You have a formula in the false portion, and then try to add your IF after that.

I have highlighted the offending section of your code in red. I didn't check to see if you do this in your other IF statement after that.
Code:
[COLOR=#333333]=IF(E9<=1625,(E9*0),IF(E9<=7000/3,(0+((E9-1625-E9*7.8%+$B$10/12+$B$11/12)*0.2)),[/COLOR][COLOR=#ff0000](425/3+(E9-7000/3-E9*7.8%+$B$10/12+$B$11/12)*0.25)),IF[/COLOR][COLOR=#333333]...[/COLOR]
I would recommend setting up your nested IF statement structure first (liked I showed in post #3), and then drop the calculations in to the appropriate sections. There should be no commas in those calculations (as that would indicate two arguments/calculations).
 

krokodilizm

New Member
OK Joe :)
Give me 12 hours to digest this. Will scrutinize this tomorrow.
Thanks.
 

Joe4

MrExcel MVP, Junior Admin
OK. If you run into trouble, post a grid like you did up in post #5, but add a column where you show us the exact formula to use for each band (what you are trying to put in your IF formula). Since you have some IF statements that have two formulas, I do not want to assume which one is correct.

Here is also a link that explains Nested IF statements in a bit more detail: Excel Nested If's
For each additional level you have, you would have another IF statement that resembles the structure of the first (not the last).
 

AlanY

Well-known Member
try this

Code:
=IF(E9<=1625,0,IF(E9<=7000/3,(E9-1625-E9*7.8%+$B$10/12+$B$11/12)*0.2,IF(E9<=3025,425/3+(E9-7000/3-E9*7.8%+$B$10/12+$B$11/12)*0.25,IF(E9<=5000,5475/12+(E9-3025-E9*7.8%+$B$10/12+$B$11/12)*0.3,10885/12+(E9-5000-E9*7.8%+$B$10/12+$B$11/12)*0.35))))
 
Last edited:

krokodilizm

New Member
try this

Code:
=IF(E9<=1625,0,IF(E9<=7000/3,(E9-1625-E9*7.8%+$B$10/12+$B$11/12)*0.2,IF(E9<=3025,425/3+(E9-7000/3-E9*7.8%+$B$10/12+$B$11/12)*0.25,IF(E9<=5000,5475/12+(E9-3025-E9*7.8%+$B$10/12+$B$11/12)*0.3,10885/12+(E9-5000-E9*7.8%+$B$10/12+$B$11/12)*0.35))))
Thanks Alan!
Though the result is not correct (must tweak some numbers) the syntax is error free.
Thanks guys for the time and effort invested. This board looks cool and highly responsive. Keep up the great work.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top