IF with MAX(COLLECT issue

Van Keuren, Jeremy
Van Keuren, Jeremy ✭✭
in Formulas and Functions

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?

· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    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

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    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

    · Share on FacebookShare on Twitter
  • Van Keuren, Jeremy
    Van Keuren, Jeremy ✭✭

    That done did it, alright. Thank you!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!