Status Function for %Achieved and Benchmark Range

TolerDo

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!

JamesB

@TolerDo

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.

TolerDo

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.

Paul Newcome

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"))

TolerDo

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.

Paul Newcome

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.

TolerDo

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