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" Commented Apr 11, 2016 at 20:48
  • You have to "nest" the if statements. I'll add a second example. Commented 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/… Commented Apr 11, 2016 at 21:13
  • I would break it into multiple calculated fields, then the final calculated field would hold your result. Commented Apr 12, 2016 at 12:54

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.