Formula issue for conditional formatting

Faz Mussa
Faz Mussa ✭✭
edited 12/09/19 in Formulas and Functions

I am trying to set up a formula to allow for conditional formatting but having some problems. 

This is the formula I'm trying:

=IF([Actual]19 >= [Target]19, IF([Actual]19 < SUM([Target]19 * 0.1), IF([Actual]19 > SUM([Target]19 * 0.1), "GREEN", "AMBER", "RED"))

but the result leaves the cell blank with no error message.

What I'm trying to get it do is:

- If Actual >= Target = 'Green'

- If Actual is less than 10% of Target = 'Amber'

- If Actual is more than 10% of Target = 'Red'


Can anyone shed some light on where I'm going wrong please?


  • Faz Mussa
    Faz Mussa ✭✭
    edited 10/31/19

    Silly me. I fixed it myself based on a previous question I posted. I should have checked that first!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hi there, the IF statement requires that the THEN come after each IF. So in your case, you listed three IFS and then three THEN's... It should look like this:

    IF(Condition, THEN, IF(Condition, THEN, ELSE))

    Try this rendition of your formula. 

    =IF([Actual]19 >= [Target]19, "GREEN", IF([Actual]19 < SUM([Target]19 * 0.1, "AMBER", IF([Actual]19 > SUM([Target]19 * 0.1, "RED")))

    That should return the correct results. Let me know if it worked! 

  • Hi Mike,

    I'm afraid it didn't work. It worked in some cells but in others the 'incorrect argument set' appeared. 

    I did find a work around though. I included an extra column to calculate 90% of the target and used this formula which works:

    =IF([Actual]@row >= [Target]@row, "Green", IF([Actual]@row >= [90%]@row, "Amber", IF([Actual]@row < [90%]@row, "Red")))

    Thanks for you help anyway. 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome. I am glad that I got you pointed in the right direction. :) 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!