Index Max / Children

Sam Swain

Hello community,

I need a formula that will return the most common value of its children. In the photo below. There are two expanded lists (Chatham and Patcham).

I want my formula to capture the most common nature of issue for each location. In this example, Chatham should return 'Power Isolation Process' and Patcham should return 'Building Fabric - Gate' in a separate sheet (shown below).

I am therefore looking for a cross sheet formula.

Please help!

Sam Swain

Hi all, I found a work around in the end and did it this way. Maybe slightly long winded but helped me ensure that even if the nature of issue max score was equal to another score in the same group, I could join them in the metrics sheet. Step 1-3 are in the source sheet. Step 4 in the metrics sheet.

Step 1 - Nature of Issue Count
=COUNTIFS([Nature of Issue]:[Nature of Issue], [Nature of Issue]@row, [Site Name Helper]:[Site Name Helper], [Site Name Helper]@row)

Step 2 - Max Count for Group
=MAX(COLLECT([Nature of Issue Count]:[Nature of Issue Count], [Site Name Helper]:[Site Name Helper], [Site Name Helper]@row))

Step 3 - Most Common Nature of Issue
=IF([Nature of Issue Count]@row = [Max Count per group]@row, [Nature of Issue]@row)

Step 4 (in Metrics sheet) - Most Common Issue
=JOIN(DISTINCT(COLLECT({Most Common NoI}, {Site Name Helper}, Site@row)), " || ")

SoS | Dan Palenchar

Here is one way to accomplish this:

1. In your first Sheet make a column that combines the Site Name Helper and Nature of Issue columns into one value. You can use JOIN() for this. I'm going to call this column Site Issue Identifier
2. Make another Sheet and list all of the potential options for Site Issue Identifier
3. Make a Cross Sheet formula from this second Sheet to the 1st Sheet and using COUNTIF() to determine the # of instances of each Site Issue Identifier
4. Identify the most common Site Issue Identifier per each site with a RANK() function in a new column I'll call Site Issue Rank
5. On your second Sheet from your Screenshot use a lookup formula to pull in the value from each Site with the highest Site Issue Rank

Done

MedaUser

Hi @Sam Swain,

I think the formula you're looking for in this situation is too ambitious for the syntax options offered. My suggestion would be to create a metrics sheet that would calculate the possible "Nature of Issues" by region first. From there, you could then create a formula in the second screenshot that reads the highest number of issues count if the metric sheet has cities in the columns and nature of issues in the rows, such as:

In Metric Sheet:

=COUNTIF({Nature of Issue Range in raw data sheet}, [Nature of Issue]@row

Under Most Common Issue column:

=INDEX({Metrics Sheet - Nature of Issue}, MATCH(MAX({Metrics Sheet - Value of NoI}), {Metrics Sheet - Value of NoI}, 0))

Sam Swain

Hi all, I found a work around in the end and did it this way. Maybe slightly long winded but helped me ensure that even if the nature of issue max score was equal to another score in the same group, I could join them in the metrics sheet. Step 1-3 are in the source sheet. Step 4 in the metrics sheet.

Step 1 - Nature of Issue Count
=COUNTIFS([Nature of Issue]:[Nature of Issue], [Nature of Issue]@row, [Site Name Helper]:[Site Name Helper], [Site Name Helper]@row)

Step 2 - Max Count for Group
=MAX(COLLECT([Nature of Issue Count]:[Nature of Issue Count], [Site Name Helper]:[Site Name Helper], [Site Name Helper]@row))

Step 3 - Most Common Nature of Issue
=IF([Nature of Issue Count]@row = [Max Count per group]@row, [Nature of Issue]@row)

Step 4 (in Metrics sheet) - Most Common Issue
=JOIN(DISTINCT(COLLECT({Most Common NoI}, {Site Name Helper}, Site@row)), " || ")