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

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 Rollup Score]@row)) < 1, "Green", IF(AVG(CHILDREN([Health Rollup Score]@row)) <= 1.66, "Red", IF(AVG(CHILDREN([Health Rollup 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?

Hello,
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
A1, A2, A3, A4, A5
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.
Ex:
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 redevelop a lot of sheets because of limits with cross references due to analyzing large amounts of data through lookup functions.
Help Article Resources
Categories
Check out the Formula Handbook template!