Issue with COLLECT and duplicate dates

RMRGSA

We are trying to achieve a formula that finds the most current Tracked task's Variance from the Baseline date. Tasks are arranged in a table/schedule in step by step order.

I'm having an issue with this formula here:

=INDEX([Baseline Variance]5:[Baseline Variance]128, MATCH(MAX(COLLECT([Task Complete Actual]5:[Task Complete Actual]128, 



    [Milestone Type Tracker]5:[Milestone Type Tracker]128, <>"")), [Task Complete Actual]5:[Task Complete Actual]128, 0))

It works as intended unless a non-Tracker task has the same Task Complete Actual date as the Tracked Task. 

So instead of showing the Baseline Variance for the Tracked Task it shows an empty cell with no error message. 

I'm thinking I need to either:

A. Add in an IF statement in the COLLECT function to filter the range more appropriately     

OR

B. Alter the Criteria/Criteria Range in the COLLECT function.

I've attempted to include a basic excel example as a visual aid, the rows that apply are the ones with 2/18/2019 in the Actual Complete column. Ideally, the yellow cell up top should read "4" but doesn't because Task 6 shares the same Actual Complete date. If the dates are different then it works as intended.

eric.o

Hello,

 

Happy to help! From my understanding, it sounds like you'd like to return the value in the "Variance" column for the row that has the highest date in the "Actual Complete" column and the "Tracked" column isn't blank. If yes, you would need to restrict the range for the MATCH and the INDEX in the same way that you're restricting the range for the MAX. So the formula could look something like this:

 

=INDEX(COLLECT([Baseline Variance]5:[Baseline Variance]128, [Milestone Type Tracker]5:[Milestone Type Tracker]128, <>""), MATCH(MAX(COLLECT([Task Complete Actual]5:[Task Complete Actual]128, [Milestone Type Tracker]5:[Milestone Type Tracker]128, <>"")), COLLECT([Task Complete Actual]5:[Task Complete Actual]128, [Milestone Type Tracker]5:[Milestone Type Tracker]128, <>""), 0))

 

Note: The provided screenshot appears to be in excel you'll want to ensure the column names match the above-stated formula and adjust accordingly if needed. 

 

Cheers, 

Eric  

Smartsheet Support