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

formula.jpg

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?

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer โœ“

    Hi @Van Keuren, Jeremy

    Try adding in the checkbox rule for this row as part of the IF statement as well:

    =IF(AND(PeriodConfirm@row = 1, Occupied@rowย = MAX(COLLECT(Occupied:Occupied, [Shelter Name]:[Shelter Name], [Shelter Name]@row, PeriodConfirm:PeriodConfirm, 1))), 1, 0)

    That way, even if the Occupied data in that row is the same as a MAX Occupied row somewhere else where the PeriodConfirm is checked, it still has to be checked in this row as well.

    Let me know if that helped!

    Cheers,

    Genevieve

    Need more information? ๐Ÿ‘€ | Help and Learning Center

    ใ“ใ‚“ใซใกใฏ (Konnichiwa), Hallo, Hola, Bonjour, Olรก, Ciao!๐Ÿ‘‹ | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer โœ“

    Hi @Van Keuren, Jeremy

    Try adding in the checkbox rule for this row as part of the IF statement as well:

    =IF(AND(PeriodConfirm@row = 1, Occupied@rowย = MAX(COLLECT(Occupied:Occupied, [Shelter Name]:[Shelter Name], [Shelter Name]@row, PeriodConfirm:PeriodConfirm, 1))), 1, 0)

    That way, even if the Occupied data in that row is the same as a MAX Occupied row somewhere else where the PeriodConfirm is checked, it still has to be checked in this row as well.

    Let me know if that helped!

    Cheers,

    Genevieve

    Need more information? ๐Ÿ‘€ | Help and Learning Center

    ใ“ใ‚“ใซใกใฏ (Konnichiwa), Hallo, Hola, Bonjour, Olรก, Ciao!๐Ÿ‘‹ | Global Discussions

  • Van Keuren, Jeremy
    Van Keuren, Jeremy โœญโœญ

    That done did it, alright. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!