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

System

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

ajs14

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.

Capture.PNG

=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

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

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.

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 re-develop a lot of sheets because of limits with cross references due to analyzing large amounts of data through lookup functions.