5

I am using SharePoint 2010.

I added a new Column and made it a Calculated Column.

Now I need to place a CASE statement in my Formula. Something like this...

CASE when [Region1] = 'A' and [Region2] = 'B' THEN
     'NO'
     when [Region1] = 'C' and [Region2] = 'D' THEN
     'YES'
     else
     'N/A'
END

So the value of my Calculated Column will be either YES or NO

7

Rewrite your formula with if-statements and nest them, something like this:

=if(AND([Region1]="A",[Region2]="B"),"NO",if(AND([Region1]="C",[Region2]="D"),"YES","N/A"))

The first if does the first test, if that statement doesn't hold: use the second if (nested in the first if).

  • 1
    isn't the ; the correct parameters separator? – Steve B Mar 1 '13 at 10:05
  • 1
    Sadly it depends on the language you are using. – Dribbel Mar 1 '13 at 14:45
  • @Dribbel really sadly, here in Brazil the separator is ; it created a lot of difficult to me. – Guilherme de Jesus Santos Mar 26 '13 at 15:00
3

You'll want to use IF statements, look at this MSDN article or this Office document for references. It also helps to mock it up in Excel and then copy/paste it into the calculated column.

Edit: just about had it figured out and Dribble beat me to the formula, nested Ifs which can go up to 7 deep.

1

The equivalent of CASE in SharePoint is CHOOSE, but it is not very flexible (you need to resort to tricks to convert your cases into numbers). Usually nested ifs work better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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