COLLECT and MAX

Options

I have been trying to find the right combination of formulas to do this in place. I am trying to report the name of a contact in the same row as a max value. I have attached an example photo to help describe my situation

In the scenario, I am trying to look up the maximum number in the Parts Column and return the managers name on that row. The formula will be in a sheet summary. In the example above, the max value would be 300 and the formula should return the contact "Corey Gill"

Any help will be appreciated. I think I am making the solution more complicated than it needs to be.

• Options

You nailed it! Thanks

• edited 09/23/20
Options

@Alexander Ford Since you we're so quick on the last one, maybe you can help me take my formulas a bit further. Below is a similar example but with a corresponding date for each entry.

I have created a formula to find the 30 day rolling average of the number of parts. It looks like this:

=AVG(COLLECT(Parts:Parts, Date:Date, >=TODAY(-30), Parts:Parts, Parts:Parts >= 1))

What I would like to have to complement this 30 day average is to identify the Manager that had the highest number of parts in the last 30 days. In the example above, it would be Corey G as the manager with the highest number of parts in the last 30 days. Any insight?

• Employee
Options

This one took a little hacking but I believe it should work. For the MATCH function, it seems you have to include the 0 at the end of the formula for non-sorted search type. In retrospect, I'm not sure why this wasn't required for the previous formula in this thread to function correctly, but someone much smarter than myself might be able to provide some feedback on that... :)

=INDEX(Manager:Manager, MATCH(MAX(COLLECT(Parts:Parts, Date:Date, >TODAY(-30))), Parts:Parts, 0))

Thanks,

Alex

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!