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

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

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • TolerDo
    TolerDo ✭✭✭✭
    edited 08/18/23

    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
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • TolerDo
    TolerDo ✭✭✭✭
    edited 08/18/23

    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