If conditioning formula

Options
Jorge Garcia
Jorge Garcia ✭✭
edited 10/17/22 in Formulas and Functions

I am trying to create a condition formula that yields red or green under the following conditions

If total A(forecast) is equal or higher than 10% of total b(budget) then red, less than 10% green.

However I am also dealing with negative growth compared to budget, I need to show show if the negative growth is between -1% to -9.999% green, greater than or equal to -10% red.

My current formula is =IF([Updated 2022 Forecast (CapEx Spent + New Forecast)]@row >= ([CapEx Budget 2022]@row * 1.1), "Red", "Green") which only account for the first part.

Any suggestions on integrating a condition for the negative growth greater or equal to -10%?


Thank you

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Jorge Garcia

    You can add an OR Function to the beginning of your formula to check for your second condition as well. Try something like this:

    =IF(OR(Forecast >= (Budget * 1.1), Forecast <= (Budget * -1.1)), "Red", "Green")

    So for your formula:

    =IF(OR([Updated 2022 Forecast (CapEx Spent + New Forecast)]@row >= ([CapEx Budget 2022]@row * 1.1), [Updated 2022 Forecast (CapEx Spent + New Forecast)]@row <= ([CapEx Budget 2022]@row * -1.1)), "Red", "Green")

    Let me know if that works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!