INDEX COLLECT MAX

Sam Swain

=INDEX(COLLECT({Nature of Issue}, {Nature of Issue Count}, MAX({Nature of Issue Count}, 1), {Site Name Helper}, Site@row), 1)

Hello, community. I am trying to solve following.

I want my formula (above) to return the nature of issue that has the most common nature of issue count for each site {Site name helper}.

It is not returning the correct answer. For example, the most common nature of issue for London is 'fire awareness' and the most common nature of issue for Bristol is 'mechanical' - but the formula above returns 'fire awareness' for every site even when it is not the most common issue at that location.

{Nature of Issue Count} in the source sheet is COUNTIFS([Nature of Issue]:[Nature of Issue], [Nature of Issue]@row, [Site Name Helper]:[Site Name Helper], [Site Name Helper]@row

Please help!

Paul Newcome

I would insert another helper column on the source sheet with something along the lines of:

=IF([Nature of Issue Count]@row = MAX(COLLECT([Nature Of Issue Count]:[Nature of Issue Count], [Site Name Helper]:[Site Name Helper], @cell = [Site Name Helper]@row)), "X")

Then adjust your INDEX/COLLECT to be more along the lines of:

=INDEX(COLLECT({Nature Of Issue}, {Site Name Helper}, @cell = Site@row, {New Helper Column}, @cell = "X"), 1)

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)), " || ")

Hudson_Miller

@Sam Swain

Hello Sam - Apologies for not being able to feed you the formula directly but this is the best I could do before heading out to the airport. Perhaps this can get you started.

Below I've made some sample data.

Then Below that I summarized that data and counted the number of times each nature of issue appears for each location

Then below that I pulled out the nature of issue that is most frequent.

The next step is to combine the formulas into one.

One item to be aware of is that if there is a tie between a nature of issue, it will only report the first it encounters.

Sam Swain

Hi Hudson, thanks for your help. It hasn't quite worked.

I think I've already done what you've suggested in a slightly different way. First I used the countif formula to get a count of the most common issues per site. This was done in the original sheet - COUNTIFS([Nature of Issue]:[Nature of Issue], [Nature of Issue]@row, [Site Name Helper]:[Site Name Helper], [Site Name Helper]@row

From I different sheet, I want to return the nature of issue that is next to the highest nature of issue count for each site. This is what I end up with in my Counts sheet when I use the formula

=INDEX(COLLECT({Nature of Issue}, {Nature of Issue Count}, MAX({Nature of Issue Count}, 1), {Site Name Helper}, Site@row), 1)

Here is my source sheet with the columns I am referencing

Hudson_Miller

@Sam Swain

Apologies - Can I get you to reply with what you think the answer needs to be given the screenshots above?

I'm struggling with the sentence "I want to return the nature of issue that is next to the highest nature of issue count for each site. This is what I end up with in my Counts sheet when I use the formula"

To confirm. - A you expecting the Most Common Issue column to be filled in? And, if so, for Burham and Durrington, given the screenshots I can see, what would you expect the correct answer to show? For Durrington it would be the "Fire Awareness - Fire Warden…" associated with 8 correct?

What's that ",1) doing in the Max section of the formula? Can it be deleted?

A final thought is to build a helper column that flags the answer you want to make the use of the index collect more binary instead of embedding the max function. If you can get that formula to work, then you can probably reverse engineer a single function that includes the formula from the helper column.

Unfortunately I'm getting on a flight and may not be able to respond as promptly as I would like.

Hudson

Paul Newcome

I would insert another helper column on the source sheet with something along the lines of:

=IF([Nature of Issue Count]@row = MAX(COLLECT([Nature Of Issue Count]:[Nature of Issue Count], [Site Name Helper]:[Site Name Helper], @cell = [Site Name Helper]@row)), "X")

Then adjust your INDEX/COLLECT to be more along the lines of:

=INDEX(COLLECT({Nature Of Issue}, {Site Name Helper}, @cell = Site@row, {New Helper Column}, @cell = "X"), 1)

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)), " || ")

Marcela

Hey @Sam Swain 

Thank you for posting your solution. I'm glad you got this resolved!