formula based on % Scheduled Elapsed, % Progress delayed and % completed

This discussion was created from comments split from: Automating a Roll up for RYG Symbols for Health.


  • ajs14
    ajs14 ✭✭
    edited 07/12/23

    Hi there @NCharleb, @Kelly Moore

    I am also trying to use a similar formula but based on % Scheduled Elapsed, % Progress delayed and % completed. I'm using a template created but trying to make a new workspace that does pull from a different workspace as I don't have full access to the other one. I do not have the tolerance sheet it is referencing.

    =IF([Start Date]@row <> "", IF(Children@row = 0, IF(AND([% Schedule Elapsed]@row = 0, [% Complete]@row = 0), "Gray", IF([% Schedule Elapsed]@row <= {Tolerances Range 1}, IF([Progress Delay]@row <= {Tolerances Range 2}, "Green", IF([Progress Delay]@row <= {Tolerances Range 3}, "Yellow", "Red")), IF([% Schedule Elapsed]@row <= {Tolerances Range 3}, IF([Progress Delay]@row <= {Tolerances Range 4}, "Green", IF([Progress Delay]@row <= {Tolerances Range 6}, "Yellow", "Red")), IF([% Schedule Elapsed]@row < 1, IF([Progress Delay]@row <= {Tolerances Range 7}, "Green", IF([Progress Delay]@row <= {Tolerances Range 8}, "Yellow", "Red")), IF([Progress Delay]@row > 0, "Red", "Green"))))), IF(SUM(CHILDREN([Health Roll-up Score]@row)) < 1, "Green", IF(AVG(CHILDREN([Health Roll-up Score]@row)) <= 1.66, "Red", IF(AVG(CHILDREN([Health Roll-up Score]@row)) <= 2.66, "Yellow", "Green")))), "")

    Would someone be able to explain or tell me what the tolerance ranges should be in order to make this formula work?

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭


    The tolerance ranges are exactly that, a range. You are trying to compare a single point of data to an entire range which will not work. I recommend using an INDEX/MATCH function if there's a specific value you are looking to compare to, or if you have multiple criteria using a COLLECT function.

    I assume you're using the minimum and maximum values within the range to compare to the independent variables, since you're working with tolerances.

    Using MAX(COLLECT()) or MIN(COLLECT()) would specify a maximum value or minimum value within a range of data.

    Ex: [Progress Delay]@row<=MAX(COLLECT("Range of values you want to pull", "Range you want to match to", "Value to match"))

    Lets say you have a parent with 5 kids

    A-1, A-2, A-3, A-4, A-5

    The MIN/COLLECT function will pull "1", to evaluate in what you're analyzing, and the MAX/COLLECT will pull "5".

    The column properties have to match as well. You can't compare a date column to a text/number column without properly converting the values first.

    You can use almost any aggregate function outside of COLLECT().

    Most of the time there is already a function to handle the same process.


    AVG(COLLECT({Tolerance},"Range to Match", "Value to match"))

    AVERAGEIF("Range to Match", "Value to match",{Tolerance})

    Keep in mind, when analyzing data through cross references, especially entire ranges like how the COLLECT function works, can minimize how well the sheet runs. I've had to re-develop a lot of sheets because of limits with cross references due to analyzing large amounts of data through lookup functions.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!