Budget Health Status

Options

Hello,

I am currently using this formula to determine budget health:

=IF([Projected Spend %]2 < 0.97, "Blue", IF(AND([Projected Spend %]2 >= 0.97, [Projected Spend %]2 <= 1), "Green", IF(AND([Projected Spend %]2 > 1, [YTD Actual Spend %]2 <= 1), "Yellow", "Red")))

I'd like to update it to the following:

-On target (97-100%) would be green

-A difference <=5% (92-96% or 101-105%) would be yellow

-A difference between 6 and 10% (87-92 or 105-110) would be red

-A difference of more than 10% (<87 or >110) would be gray

Thanks!

Nathan

Answers

  • Sean Morgan
    Options

    Hi @Nathan Umbriac ,

    I'd love to help create a Formula around this!

    I have a question that should help me determine how to construct this formula. I assume that the variables of percentage differences are between Project Spend, and YTD Actual Spend?

    I was able to create an Example below, but let me know if I've missed anything!

    The Formula I used was:

    =IF(AND([Projected Spend %]@row >= 0.97, [Projected Spend %]@row <= 1), "Green", IF(AND([Projected Spend %]@row >= 0.92, [YTD Actual Spend %]@row <= 0.96), "Yellow", IF(AND([Projected Spend %]@row >= 1.01, [YTD Actual Spend %]@row <= 1.05), "Yellow", IF(AND([Projected Spend %]@row >= 0.87, [YTD Actual Spend %]@row <= 0.92), "Red", IF(AND([Projected Spend %]@row >= 1.05, [YTD Actual Spend %]@row <= 1.1), "Red", IF(AND([Projected Spend %]@row < 0.87, [YTD Actual Spend %]@row > 1.1), "Gray"))))))

    Here's a Screenshot of it meeting the conditions:

    Let me know if you have any questions :D

    Regards

    Sean

  • Nathan Umbriac
    Nathan Umbriac ✭✭✭✭✭
    edited 11/09/20
    Options

    Thank you, @Sean Morgan . The formula seems to work for most of my rows, but gray is not appearing. I updated the symbol column to the four symbol colors, but am I missing something else?

  • Nathan Umbriac
    Nathan Umbriac ✭✭✭✭✭
    Options

    I updated the formula to:


    =IF(AND([Projected Spend %]@row >= 0.97, [Projected Spend %]@row <= 1), "Green", IF(OR(AND([Projected Spend %]@row >= 0.92, [Projected Spend %]@row < 0.96), AND([Projected Spend %]@row >= 1.01, [Projected Spend %]@row < 1.05)), "Yellow", IF(OR(AND([Projected Spend %]@row >= 0.87, [Projected Spend %]@row < 0.92), AND([Projected Spend %]@row >= 1.05, [Projected Spend %]@row < 1.1)), "Red", IF(OR([Projected Spend %]@row < 0.87, [Projected Spend %]@row >= 1.1), "Gray"))))


    Seems to be working now.

  • Sean Morgan
    Options

    Hey @Nathan Umbriac !

    Apologies for the late reply, I was OOO Yesterday.

    Im glad this is all up and running now!

    Let me know if you have any questions

    Regards

    Sean

  • Nathan Umbriac
    Nathan Umbriac ✭✭✭✭✭
    Options

    @Sean Morgan

    I amended the formula to:

    =IF(AND([Projected Spend %]@row>= 0.97, [Projected Spend %]781 <= 1), "Green", IF(OR(AND([Projected Spend %]@row>= 0.92, [Projected Spend %]@row<= 0.96), AND([Projected Spend %]@row>= 1.01, [Projected Spend %]@row<= 1.05)), "Yellow", IF(OR(AND([Projected Spend %]@row>= 0.87, [Projected Spend %]@row<= 0.92), AND([Projected Spend %]@row>= 1.05, [Projected Spend %]@row<= 1.1)), "Red", IF(OR([Projected Spend %]@row<= 0.87, [Projected Spend %]@row>= 1.1), "Gray"))))

    However, I have a scenario where the projected spend percentage is 96%, which gives me a blank cell.

    Any thoughts?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Nathan Umbriac

    In the formula 2 above you don't have an = sign with the  [Projected Spend %]@row<= 0.96, which is likely why you received a blank return for 96%, however when I tested this most recent formula, it gave me a yellow response as expected.

    Can you clarify which formula you're using? In the second formula, the beginning Green statement doesn't have @row for one of the percentages:

    =IF(AND([Projected Spend %]@row>= 0.97, [Projected Spend %]781 <= 1), "Green",

    You'll need to change that 781 to @row, then it should work as you'd like!

    =IF(AND([Projected Spend %]@row>= 0.97, [Projected Spend %]@row <= 1), "Green",


    Let me know if that works for you.

    Cheers,

    Genevieve

  • Nathan Umbriac
    Nathan Umbriac ✭✭✭✭✭
    Options

    Thank you, @Genevieve P. Here is the formula I currently have:


    =IF(AND([Projected Spend %]@row >= 0.97, [Projected Spend %]@row <= 1), "Green", IF(OR(AND([Projected Spend %]@row >= 0.92, [Projected Spend %]@row <= 0.96), AND([Projected Spend %]@row >= 1.01, [Projected Spend %]@row <= 1.05)), "Yellow", IF(OR(AND([Projected Spend %]@row >= 0.87, [Projected Spend %]@row <= 0.92), AND([Projected Spend %]@row >= 1.05, [Projected Spend %]@row <= 1.1)), "Red", IF(OR([Projected Spend %]@row <= 0.87, [Projected Spend %]@row >= 1.1), "Gray"))))

    As noted above, I have a row with a projected spend of 96%, but the result is still a blank cell.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Nathan Umbriac

    I've tested on my own sheet and as long as the Projected Spend % column is a Text/number column that's formatted to be a % column, 96% shows as "Yellow", based on your instructions above.

    I would suggest making this formula a Column Formula to make sure the exact same format & structure is applied through the whole sheet.

    Would it be possible to see a screen capture of this row in your sheet, with the formula open (so I can see it highlighted in the different colours)? But please block out any sensitive data!

    Cheers,

    Genevieve

  • Nathan Umbriac
    Nathan Umbriac ✭✭✭✭✭
    Options

    Thanks @Genevieve P

    I've included a screen shot. I cannot do a Column formula as there are several sub tasks where the formula isn't applicable.


  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/16/20
    Options

    Hi @Nathan Umbriac

    That's definitely strange; at this point it's not about how your formula is written, so now there are a number of troubleshooting steps to try.

    The first one is that I would adjust all your row references to @row instead of row 781. @row means that the formula doesn't have to read through your entire sheet to find this row, 781 down. It can just operate by looking in this current row, which will help with sheet performance.

    Next I would check the cell that has 96% to ensure that it's 0.96 with a % format (and not somehow being read as text).

    Then, I would save the sheet, log out of Smartsheet, and log back in. I would drag-fill the formula up and down to fill the cell again, in case this refreshes the formula and it then works as expected.

    Finally, if none of that has worked, you may want to reach out to Smartsheet Support, since your formula is built correctly and it works on my sheet.

    I hope one of these steps helps!

    Genevieve