Budget Health Status

Nathan Umbriac

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

Sean Morgan

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

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

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

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

@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?

Smartsheet legend.PNG

Genevieve P.

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

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.

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

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.

smartsheet.docx

Genevieve P.

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