COLLECT function Criteria Not Working

Brad Dyck

I am attempting to identify the bottom 2 ranked records from the "Progress" column but I only want to count records that are checked in the "ProMO-Objectives" column. 

I am trying to identify the bottom records in the "ProMO Rank" column using a combination of IF, SMALL and COLLECT functions (formula below). The SMALL part of the function seems to be working as it is identifying the bottom 2 records however the criteria part (ProMO-Objectives=1) doesn't work as its ranking rows that are not checked in the "ProMO-Objectives" column. 

I am using the following formula to check/uncheck "ProMO-Objectives" column and it works fine: =IF(AND(Programme@row = "Core - ProMO", Layer@row = 0), 1, 0)

THIS FORMULA DOESN'T WORK: Formula in the "ProMO Rank" column is: =IF(Progress@row < SMALL(COLLECT(Progress:Progress,ProMO-Objectives:ProMO-Objectives, =1), 2), "True", "FALSE")

It returns True/False but ignores the criteria.

I don't know what is wrong with my COLLECT function. Also attempted putting multiple criteria in the collect function with the same result - it collected and ranked all rows, ignoring the criteria portion of the formula (Layer@row=0):

 =IF(AND((Progress@row < SMALL(COLLECT(Progress:Progress, Programme:Programme, "Core - ProMO"),2), Layer@row = 0), "True", "False")

If anyone has an actual example of using COLLECT with multiple criteria I'd really appreciate it.

Ultimately my goal is to be able to identify the top 2 and bottom 2 records by progress.

Alasdair Cockburn

This formula...

=IF(Progress@row < SMALL(COLLECT(Progress:Progress,ProMO-Objectives:ProMO-Objectives, =1), 2), "True", "FALSE")

Is identifying anything in the Progress column that is smaller than the 2nd smallest row that has been ticked as ProMO-Objectives. So the second smallest ProMO-Objective is 54% and the rows that have progress less than 54% are being marked as True.

To Only look at rows identified as ProMO-Objectives you would need to add another nested IF function

=IF(ProMO-Objectives@row = 1, IF(Progress@row <= SMALL(COLLECT(Progress:Progress,ProMO-Objectives:ProMO-Objectives, =1), 2), "True", "FALSE"), "FALSE")

This should only identify the items marked as ProMO-Objectives and smaller than or equal to the second smallest Progress value.

Brad Dyck

SMALL FUNCTION ISSUE RESOLVED:

Thank you very much! I did get the formula working to identify the bottom 2 in the list thanks to your suggestion using the following formula:

=IF(Layer@row = 0, IF(Progress@row <= SMALL(COLLECT(Progress:Progress, Programme:Programme, ="Core - ProMO"), 3), "Bottom 2", "FALSE"), "")

ISSUE NOW WITH LARGE FUNCTION:

I am now running into issues identifying the top 2. I sim

ply changed the "SMALL" to "LARGE" and changed my comparison operator to greater than or equal to but now it appears that it's checking ALL progress rows, not just the Layer =0:

=IF(Layer@row = 0, IF(Progress@row >= LARGE(COLLECT(Progress:Progress, Programme:Programme, ="Core - ProMO"), 3), "Top 2", "inside false"), "")

When I identify the 4th top progress value using the following formula, it returns 0.75 as if the COLLECT function is checking/ranking ALL rows instead of only the rows with Layer=0. 

=IF(Layer@row = 0, LARGE(COLLECT(Progress:Progress, Programme:Programme, ="Core - ProMO"), 3), "inside false")