0

I am using the below excel formula, but getting an error that "you have entered too many arguments for this function". Is there any other way I can make this function work please?

Please excuse for this basic question. I am no expert in excel.

IF((OR(AF2="CitiCMT", AF2="AML APAC ATHENA", AF2="AML EMEA", AF2="AML Americas")),"AML","", IF((OR(AF2="CitiScreening", AF2="Offshore Wealth Services", AF2="CitiScreening China")),"CitiScreening","", IF((OR(AF2="CitiScreening", AF2="Offshore Wealth Services", AF2="CitiScreening China")),"CitiScreening","", IF((OR(AF2="Registrations", AF2="Trade Surveillance - Markets", AF2="Reg-Reporting", AF2="Trade Surveillance - IBSG", AF2="eCADD", AF2="EmDD", AF2="Trade Surveillance", AF2="Trade Surveillance - Retail")),"Core Compliance",""))))
  • You have included the same "citiscreening" condition twice, I'm assuming this is a mistake and so I'll act as if it's not there. – Some_Guy Feb 7 '17 at 10:10
  • Consider checking out what to do when someone answers my question if any of the answers did help you out with understanding your problem. – Seth Apr 11 '17 at 6:08
2

Another way to write your function would be this. Every level would be an argument to the function one level above it. If you look at the documentation for IF Function and OR Function you will see that you, as the error says, supplied to many arguments to some of those functions. Namely some of the if functions. The only correct IF seems to be the deepest nested one, every other instance has 4 arguments. If you remove the "", entries it should work.

IF(
    (
        OR
        (
            AF2="CitiCMT",
            AF2="AML APAC ATHENA",
            AF2="AML EMEA",
            AF2="AML Americas"
        )
    ),
    "AML",
    "",
    IF
    (
        (
            OR
            (
                AF2="CitiScreening",
                AF2="Offshore Wealth Services",
                AF2="CitiScreening China"
            )
        ),
        "CitiScreening",
        "", 
        IF
        (
            (
                OR
                (
                    AF2="CitiScreening",
                    AF2="Offshore Wealth Services",
                    AF2="CitiScreening China"
                )
            ),
            "CitiScreening",
            "",
            IF
            (
                (
                    OR
                    (
                        AF2="Registrations", AF2="Trade Surveillance - Markets",
                        AF2="Reg-Reporting", AF2="Trade Surveillance - IBSG",
                        AF2="eCADD", AF2="EmDD", AF2="Trade Surveillance",
                        AF2="Trade Surveillance - Retail"
                    )
                ),
                "Core Compliance",
                ""
            )
        )
    )
)
2

I would set up a table with your possible inputs and desired output on a different sheet:

enter image description here

I called mine Codes

Then it is a simple matter of a VLOOKUP to return the desired string:

=IFERROR(VLOOKUP(AF2,Codes!A:B,2,FALSE),"")

This is now easily expandable if there are new inputs and outputs, without the need to change the formula. One would just add the criteria nad outputs to the table.

0

Your problem is the way you are chaining the if statements together.

An if statement in excel if is always in the format:

=IF(CONDITION,VAL IF TRUE, VAL IF FALSE)

You have them chained together like

=IF1(CONDITION1,VAL IF TRUE, VAL IF FALSE, IF2(CONDITION2,...))

This doesn't make any sense, because you're saying

=IF1(CONDITION1,VAL IF TRUE, VAL IF FALSE, SOMETHING ELSE)

To chain together if statements, you have two options:

  1. Make each subsequent if statement the FALSE condition of the previous if statement. So you're saying "If something is true then output this. If not then move onto the next if statement". in excel language either:

    =IF(CONDITION1,OUTPUT1,IF(CONDITION2,OUTPUT2,IF(CONDITION3,OUTPUT3,"")))

  2. Concatenate a bunch of if statements together. So that's like saying "if something is true then output this, if not then output nothing. Also, if something else is true then output this.

    =IF(CONDITION1,OUTPUT1,"") & IF(CONDITION2,OUTPUT2,"") & IF(CONDITION3,OUTPUT3,"")

Note that in the first case, as soon as one of the conditions in met, the formula is complete, whereas in the second case, all conditions are always tested. This means that:

  • method 1 is faster

  • method 2 works when more than one of the conditions can be true.

So with your example you would have either

 =IF(OR(AF2="CitiCMT", AF2="AML APAC ATHENA", AF2="AML EMEA", AF2="AML Americas"),"AML",
  IF(OR(AF2="CitiScreening", AF2="Offshore Wealth Services", AF2="CitiScreening China"),"CitiScreening",
  IF(OR(AF2="Registrations", AF2="Trade Surveillance - Markets", AF2="Reg-Reporting", AF2="Trade Surveillance
  - IBSG", AF2="eCADD", AF2="EmDD", AF2="Trade Surveillance", AF2="Trade Surveillance - Retail"),"Core Compliance","")))

or

 =IF(OR(AF2="CitiCMT", AF2="AML APAC ATHENA", AF2="AML EMEA", AF2="AML Americas"),"AML","") & 
  IF(OR(AF2="CitiScreening", AF2="Offshore Wealth Services", AF2="CitiScreening China"),"CitiScreening","") & 
  IF(OR(AF2="Registrations", AF2="Trade Surveillance - Markets", AF2="Reg-Reporting", AF2="Trade Surveillance - IBSG",
  AF2="eCADD", AF2="EmDD", AF2="Trade Surveillance", AF2="Trade Surveillance - Retail"),"Core Compliance","")

In your case, a cell can't be both "citiscreening" and "registrations" so it makes more sense to use the first one, although if speed isn't that important, the second one can be easier to read, and easier to modify, because there isn't a bunch of brackets to match up.


tips:

  • Note that you can break a formula across lines by pressing alt+enter, this will make it easier to read and doesn't affect the output of the formula.

  • Excel provides a handy toolyip which shows you which argument your cursor is at by bolding it. Also, if you click on the argument, it will highlight that part of the formula.

In your original function, you can see that there were too many arguments, because after the first "", nothing is bolded.

enter image description here

enter image description here


in case it's useful, here's the above answer in pseudocode:

Option 1.

if (condition1) then   
    output1
    else if (condition2) then
        output2
            else if (condition3) then
                output3
                    else output nothing

Option 2:

if (condition1) then   
    output1
else 
    output nothing

if (condition2) then
    output2
else 
    output nothing

if (condition3) then
    output3
else 
    output nothing

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.