1

I am trying to compare text in two columns to auto calculate a result in a third calculated column (represented by an image).

The logic is as follows.

If column1 value is Green and Column2 is Yellow - then I want to display a down arrow image.

If column1 is Yellow and Column2 is Green - then I want to display an up-arrow image.

.... and so on, with various examples.

Just to experiment, I tried to write the formula for the first example, but keep getting syntax errors. Any chance I can get help to debug and figure out how to include multiple options in the same formula?

Here's what I came up with (which produces syntax error).

=IF( ( [Prior Status]="Green"
       , AND( [Current Status]="Yellow"
              ,"<img src='https:---URL HERE---down.png'></img>")`

Am I even close? I am not a programmer, and don't really understand the nuances of syntax, but like a monkey I can learn and copy :-) - with some guidance.

Thanks in advance for any help you can provide.

Improve this question

1 Answer 1

Reset to default
1 
=IF( AND( [Prior Status]="Green"
          ,[Current Status]="Yellow"
        )
     ,"Value if true"
     ,"Value if false"
   )

=IF( AND(
          [Prior Status]="Green",
          [Current Status]="Yellow"
         )
     ,"Value if true"
     ,IF(
          AND(
              [Prior Status]="Yellow",
              [Current Status]="Red"
             )
          ,"Value if second condition is true"
          ,"Value if second condition is false"
         )
    )

So you would continue to use an "IF" statement for the "value if false" until you've added all of your statements.

Improve this answer
4
  • I was able to make this work for one example, but seem to run into problems when I try and add another example (prob. just subtle syntax issues again). So if I wanted to repeat the above example several times over with slightly different outcomse (in the same calculated column) e.g. =IF( AND( [Prior Status]="Grey" ,[Current Status]="Yellow"
    – Josephine
    Apr 11, 2016 at 20:48
  • You have to "nest" the if statements. I'll add a second example.
    – Erin L
    Apr 11, 2016 at 20:49
  • Have you run into issues with more than 7 IF statements. There are 12 possibilities (up to 7 by default, but can then I can then add a concatonate option )))))))&IF to do many more - in theory. Here's where I got the idea you can extend the number of IF statements past 7. blog.pathtosharepoint.com/2010/04/12/…
    – Josephine
    Apr 11, 2016 at 21:13
  • I would break it into multiple calculated fields, then the final calculated field would hold your result.
    – Erin L
    Apr 12, 2016 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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