Budget Health Status
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
-
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
-
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?
-
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.
-
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
-
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?
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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.
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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.
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives