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"))
-
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.
-
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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives