Status Function for %Achieved and Benchmark Range
Hello Community,
I have 4 columns:
Actual delivered = the number of deliverables completed
Benchmark = the number of deliverables expected
% Achieved = Percent Achieve (divides the actuals/benchmark)
Status = This is the formula I need assistance with
I'm trying to apply the red/yellow/green status indicator symbols when the %Achieved is within a certain range of the benchmark:
Green = 100% of benchmark met
Yellow = between 0.1% and 19.9% below benchmark
Red >= 20% below benchmark
I'm struggling how to write this function -- would appreciate your expertise!
Answers
-
Below are the individual formulas for each color.
For Green=
=IF([%Achieved]@row=1,"Green")
For Yellow
=IF(AND([%Achieved]@row >= 0.01, [%Achieved]@row <= 0.199), "Yellow")
For Red
=IF([%Achieved]@row>=.2,"Red")
Now bring them into one formula:
=IF([%Achieved]@row = 1, "Green", IF(AND([%Achieved]@row >= 0.01, [%Achieved]@row <= 0.199), "Yellow", IF([%Achieved]@row >= 0.2, "Red")))
If none of the conditions are met the cell will be blank. For example 0% will be blank.
-
Thank you so much! I won't even tell you how long I was running in circles.
Only problem is, I have 5 different benchmarks through the duration of the project, and we're monitoring progress against each benchmark.
So, when they get ahead (achieving greater than the benchmark, so >1), I get a red status when I try to apply this formula.
I'm likely overlooking something.
-
Are each of the 5 benchmarks in their own column on each row? Are you able to provide a screenshot for some context?
Green = 100% of benchmark met
Yellow = between 0.1% and 19.9% below benchmark
Red >= 20% below benchmark
Here is what I would suggest for the base formula:
=IF([% Achieved]@row = [Benchmark %]@row, "Green", IF([% Achieved]@row<= [Benchmark %]@row - 0.20, "Red", "Yellow"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Each benchmark is in it's own column -- this is just a mockup.
So I'm looking for the formula mentioned above to determine status based the threshold range.
-
How do you determine which benchmark is the driving benchmark for the RYG? With having all zeros and no other sample data, it is hard to determine exactly how things are meshing together.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Sorry about that. I got it figured out. Sometimes the simplest way is the best. I was waaaay over thinking it -- my % achieved calculation is already is based on the current workset, it was super easy to get the status using my tolerance ranges.
Now I'm just trying to figure out how to make the function recognize the dates of worksets, so we can calculate % achieved based on active workset benchmarks as they become current.
It's always something! I'm working on that, and will come back to community if I get lost.
You all are awesome! Thanks
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives