Properly using Max(collect)

PM Assist

I am trying to get Column37 to output the value in the "Is the employee currently off" column.

I don't understand why this is not finding a match.

I have done this exact same thing in another sheet and it works perfectly but for some reason, this is not matching

Here is the other one, which works for some reason.

PM Assist

Update:

Somehow I solved it by adding and referencing a worker column that was =IF([is the employee currently off?]@row = 1, 2, 1).

I still don't understand why this change worked - would you be able to help me understand this Paul?

Paul Newcome

How is the data in the "Is the employee currently off" column being populated?

You have the #NO MATCH error in the first column. Getting rid of that error should clear up the error in your MAX/COLLECT.

PM Assist

Hi Paul,

This cleared up the error in my MAX/COLLECT but ran me into another issue.

Why is my max(collect) function not returning 1 for cases where [Is the employee currently off?] is 1?

Since [Is the employee currently off?] can only be 1, shouldn't column37 output a 1 whenever [Is the employee currently off?] is 1

PM Assist

Update:

Somehow I solved it by adding and referencing a worker column that was =IF([is the employee currently off?]@row = 1, 2, 1).

I still don't understand why this change worked - would you be able to help me understand this Paul?

Paul Newcome

Exactly how is the data populated in the [Is the employee currently off?] column?

PM Assist

[Is the employee currently off?] is populated by comparing today's date to the employee's requested time off window: =IF(AND([Start Date]@row < TODAY(), TODAY() < [End Date]@row), "1", "0").

Here, [start date] is representative of the first day being taken off and [End date] is representative of the last day being taken off.

Paul Newcome

Try removing the quotes from around those numbers. The quotes are outputting a text value. Without the quotes it outputs a numerical value (which is why the reference formula in the helper column worked).