How do I do a min/max output based upon a specific criteria?

So I think I've come close to this in the area of indexing, matching, etc., but I can't quite get the formula to work the correct way.

I have a sheet setup to where it calculates the average number of days that specific jobs have been open and working within several different groups in my organization. Each organization has a different 3 letter identifier. What I want to do is, on a different sheet, just have two different lookups for each organization that identifies the smallest number of days, and the largest number of days, that each jobs has been open, based upon their organizational code, without having to highlight the ranges manually (but instead use the columns) since we add rows quite frequently.

I've included a picture of the type of data I'm working with, and the type of out put I'm trying to come up with, if it's possible to do so cross sheets.

I can get as far as this lookup

=INDEX({FY22 Hiring Tracker Range 2}, MATCH(MAX({FY22 Hiring Tracker Range 2}), {FY22 Hiring Tracker Range 2}))

which outputs the highest number in the days open column, but I cannot figure out how to make it reference the org code column for the life of me.

Any help would be greatly appreciated, idk if I'm just over thinking this.

Find more posts tagged with

Human Resources

Formulas

Accepted answers

Christina09

Hi @Marc Bills

I'm not sure if I'm understanding it right, but if you want a formula for finding MIN and MAX for this

Here is the formula:

=MIN(COLLECT({Days Open},{Org Code},Parent(Column@row)))

=MAX(COLLECT({Days Open},{Org Code},Parent(Column@row)))

Collect({cross sheet reference to days open column},{cross sheet reference to org code column}, Parent(the @row parent with 3 letter identifier))

Hope this helps.

Have a nice weekend.

All comments

Christina09

Hi @Marc Bills

I'm not sure if I'm understanding it right, but if you want a formula for finding MIN and MAX for this

Here is the formula:

=MIN(COLLECT({Days Open},{Org Code},Parent(Column@row)))

=MAX(COLLECT({Days Open},{Org Code},Parent(Column@row)))

Collect({cross sheet reference to days open column},{cross sheet reference to org code column}, Parent(the @row parent with 3 letter identifier))

Hope this helps.

Have a nice weekend.

Marc Bills

Thanks so much for getting me to my solution! Weirdly enough when I removed the 'Parent(Column@row)' bit and replaced it with the name of the org, it finally worked. My final formula was

=Max(Collect({Days Open}, {Org Code}, "3 letter org"))

and it finally worked. Thanks so much for getting me to the correct =formula set!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.