0

I'm not sure if it can be done, please note I am not a novice, I am trying to join multiple syntax for a calculated column.

I have column 1 as 'state' its values are either California or New York and in Column 2 as 'region' its values are East, West or Special and I need to assign an employee. Employee Jane gets all of California as long as the region is not special and John gets all of New York as long as the region is not special but Joe is assign the special region regardless of state.

My first syntax works great but when I try to join them, it is not being accepted.

Primary Syntax:

=IF(AND([State]="California",[Region]<>"Special"),"Jane","""")

My failed attempt at joining them.

=IF(AND([State]="California",[Region]"Special"),"Jane","""",=IF(AND([State]=
"New York",[Region]"Special"),"John","""",=IF(AND([region]="Special","Joe")))
|improve this question
  • There's a few syntax issues here... But nothing too major. You don't need all the = signs, and you've got 4 terms in each IF statement, not 3, and your last IF statement just has one boolean term saying If Region is Special AND Joe where I think you want If Region is Special, THEN Joe. I wrote an answer below giving what I think should work for your situation. – KGlasier Dec 6 '18 at 18:38
0

Use this:

=IF(AND([State]="California",[Region]<>"Special"),"Jane", IF(AND([State]="New York",[Region]<>"Special"),"John", IF([region]="Special","Joe", "")))
|improve this answer
0

I think you may be over thinking everything. Try something like this:

=IF(
    [region]="Special",
    "Joe",
    IF(
        [State]="California",
        "Jane",
        IF(
            [State]="New York",
            "John",
            ""
        )
    )
)

This should make all Specials go to Joe, otherwise Californias go to Jane. If it's neither Special nor California, but the State is New York go to John. If it doesn't follow any of those requirements then it'll be left blank.

If your states were exclusively California and New York you can replace the section IF([State]="New York","John","") with just "John" and it should act the same. But if you have more states involved, then you'll need to keep that IF statement as-is.

Here's the version without spaces if you'd ranter:

=IF([region]="Special","Joe",IF([State]="California","Jane",IF([State]="New York","John","")))
|improve this answer
  • Your answer has logical issues. By my opinion. If State will be Texas and Region not Special then you get John .... John only in New York. Why ? You must get empty value.... – Vsevolod Vasilev Dec 6 '18 at 18:50
  • 1
    @VsevolodVasilev It would, except that the column State was stated to be exclusively California or New York. I'll change it to be more general, but it does solve OP's question. – KGlasier Dec 6 '18 at 19:56

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.