COLLECT function Criteria Not Working
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.
Help Article Resources
Check out the Formula Handbook template!