COLLECT and MAX

COREY GILL

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.

Alexander Ford

@COREY GILL I believe that combining INDEX, MATCH and MAX will get you what you're looking for. This sample formula is using INDEX to evaluate the Manager column, and combining MATCH(MAX) to determine the highest Parts value to return as the row to return for the INDEX formula, which should get you exactly what you're looking for. There are probably other ways to do this, but this should work.

=INDEX(Manager:Manager, MATCH(MAX(Parts:Parts), Parts:Parts))

Hope that helps!

-Alex

Alexander Ford

@COREY GILL I believe that combining INDEX, MATCH and MAX will get you what you're looking for. This sample formula is using INDEX to evaluate the Manager column, and combining MATCH(MAX) to determine the highest Parts value to return as the row to return for the INDEX formula, which should get you exactly what you're looking for. There are probably other ways to do this, but this should work.

=INDEX(Manager:Manager, MATCH(MAX(Parts:Parts), Parts:Parts))

Hope that helps!

-Alex

COREY GILL

You nailed it! Thanks

COREY GILL

@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?

Alexander Ford

@COREY GILL

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