Numbers formula

giordano100 wrote:

I know there is an excel formula for this that uses INDEX, MATCH and MODE but it does not work in Numbers for Mac. It would be amazing if we can get this to work.

You've kept us in suspense. What is the formula in Excel to which you refer?

This is not a single-formula solution (perhaps that is possible in Excel because it can handle "array" formulas that Numbers does not support) but here is one way to find the most commonly occurring value in a column in a table:

Names in column A.

Formula in B2, filled down:

=COUNTIF(A,A2)

Formula in B11:

=MAX(B)

Formula in A11:

=XLOOKUP(B$11,B,A)

Row 11 is defined as a Footer Row.

Replace the , in the formulas with ; if your region uses , as a comma separator.

SG

Here is one way.

In B2 of Table 2, filled down:

=COUNTIF(Table 1::$B$2:$F$6,A2)

The last row is defined as a Footer Row. The formula in C5 is:

=XLOOKUP(MAX(B),B,A)

In Column A of Table 2 you need to list all the names in Table A.

SG

The formulas SGIII and I posted the first time and the Excel formula I found on the Internet all will find the most commonly occurring name in a table if all those names are a single column. None work for an array of names. I'd like to see the Excel formula you are referring to that works on an array of names. It might lead to a better/different answer here.

SGIII has now posted an easy way to do it if you have a separate list of all the names. Below is a method that does not require the names to be placed in a list. It is more complicated, though, and has the limitation of 999 columns and 999 rows. I did not design it for your exact table, it is only an example. And there may be a better way to do it; there often is.

Table 2::B2 =COUNTIF(Table 1::$B$1:$E5,Table 1::B2)+ROW()÷1000+COLUMN()÷1000000

drag-fill to the other data cells in the table.

This counts the occurrences of that particular name and appends the row number/1000 and column number/1000000 so that every number in the table will be unique. The largest number in the table indicates the location of the name occurring most frequently.

Table 2::B1 =MAX(B)=MAX($B:$E)

fill to to the right to complete the row

Table 2::A2 =MAX(2:2)=MAX($B:$E)

fill down to complete the column

Table 2::A1 (the answer) =OFFSET(Table 1::A$1,MATCH(TRUE,A2:A5,0),MATCH(TRUE,B1:E1,0))

This will work okay as long as the size of the array of names in Table 1 stays the same. If you change it, you also have to modify Table 1 and the formulas in it so it matches up. You can put Table 2 out of the way on another sheet and reference cell A1.

giordano100 wrote:

Is it possible to input a function that says only show if not joint max? For example if NameA and B occur the same amount it will display 0.

Try replacing the formula in C5 of Table 2 of the example to:

=IF(COUNTIF(B,MAX(B))>1,0,XLOOKUP(MAX(B),B,A))

This tests to see if the largest value in column B occurs more than once. If it does, a 0 is displayed. If it doesn't (i.e. it occurs once) then the name is displayed as in the example.

SG

Ported to Numbers using the same method it would be

column B formula =MATCH(B,B)

D2 =INDEX(A,MODE(B))

Likely this, which is an array formula: