Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: IF with multiple arguments
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF with multiple arguments

    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.

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: IF with multiple arguments

    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))))) ?

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,024
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: IF with multiple arguments

    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)))
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    New Member
    Join Date
    Feb 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF with multiple arguments

    So there is definitely an IF missing Let me see...

  5. #5
    New Member
    Join Date
    Feb 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF with multiple arguments

    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/3 20 425/3
    7001/3-3025 25 3775/12
    3026-5000 30 10885/12
    over 5000 35

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

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,024
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: IF with multiple arguments

    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:
    =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...
    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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Feb 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF with multiple arguments

    OK Joe
    Give me 12 hours to digest this. Will scrutinize this tomorrow.
    Thanks.

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,024
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: IF with multiple arguments

    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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: IF with multiple arguments

    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 by AlanY; Feb 3rd, 2016 at 10:32 AM.

  10. #10
    New Member
    Join Date
    Feb 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF with multiple arguments

    Quote Originally Posted by AlanY View Post
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •