# Formula Syntax/Logic help

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.

## 1 Answer

``````=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.

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