IF with MAX(COLLECT issue
Hello! I am stumped by a problem I'm working on. Part of my work is to assemble a dashboard that shows the maximum number of occupied beds at several homeless shelters in a given period of time during the previous night. Supervisors at each shelter, hour to hour, complete a survey that asks the number of occupied beds ([Occupied]). Each shelter has its own name ([Shelter Name]). So, a submitted survey adds a line to the top of the
grid that tells me the shelter name, how many occupied beds, and a timestamp (which I converted to military time, but I don't think that matters here). If the survey is submitted during the period I want to monitor, there is a checkbox column ([PeriodConfirm]) that is automatically checked.
I have a checkbox column ([Nighttime Peak]) that SHOULD be checked ("true") if the number in [Occupied] is the highest number that ALSO has [PeriodConfirm] checked. The formula is:
=IF(Occupied@row = MAX(COLLECT(Occupied:Occupied, [Shelter Name]:[Shelter Name], =[Shelter Name]@row, PeriodConfirm:PeriodConfirm, true)), 1, 0)
Here's the problem: it's not working right, and I can't figure out why. The [Nighttime Peak] column is sometimes checked even if [PeriodConfirm] is not. Any idea why?
Help Article Resources
Check out the Formula Handbook template!