MODE for Text Values, Extract Word That Occurs Most Frequently

Hi Todd,

I am not sure of your setup. Are the values you are counting in seperate cells in the same row? How many values are there?

MODE() will only operate on numerical values in Numbers. You will need an alternative approach.

What is your ultimate aim? Why are you counting these values and how are you going to use them?

quinn

Hi Todd,

Since Numbers can't handle most array operations, unfortunately you can't use that nifty Excel formula.

You, can however, easily calculate the frequencies using COUNTIF, like this:

SG

Without the power array functions you can end up using a lot of space to lay out the arrays in a table. Here I think you will need two columns for each set of values, something like this:

The formula I've used in A6 that looks up the most frequently occurring value is:

=INDEX(A,MATCH(MAX(B),B,0))

The last row is defined as a Footer Row.

You could then hide the Frequency columns if you want.

SG

Hi Todd,

It looks like SG has you pretty well set up. I like keeping the frequency counts on their own table. That whole table could be hidden on a far sheet. Set it up on the sheet with the data then cut and paste it to somewhere out of the way.

This is what it might look like if you kept it in rows and in a single table.

You can see it is the same approach as SG's just orientated differently.

The countif is also the same

With multiple tables the formulas would simplify because you would not use partial rows.

quinn

Todd,

Are you saying you want to look up the items in column A of the Summary table by rank? The summary table already has the list of values and the frequency. Can you guarantee that there are no two frequencies that will be the same? If not then there are some extra hoops to jump through to guarantee an unambiguous ranking.

If you add the table named "Rank" (bottom right)

A1=LARGE(Summary::B,ROW())

B1=OFFSET(Summary::$A$1,MATCH(A1, Summary::B)−1, 0)

select A1 and B1, copy

select columns A and B, paste

Todd Lichtenwalter1 wrote:

Is it possible to make it work when I don't know the text responses in advanced. For example I have a row that ask what alternative medicine they use. The responses could be anything so it is possible to have it search for the most common response when its not predefined?

You can extract a list of distinct values from a range (which contains, for example, the alternative medicine responses) with this Copy Distinct Automator Service (Dropbox download).

To install double-click the .workflow package and if necessary click Open Anyway in System Preferences > Privacy and Security.

To use, simply select the range, choose Copy Distinct from the Numbers > Services menu, click once in the top destination cell where you want the values to appear, and command-v to paste. Then you can place COUNTIF formulas in the adjacent column.

SG

By adjacent column I meant the same setup you have: put amother Frequency column in D and add COUNTIF formulas the way you have in column B, except referring to a different column in the Data table. So instead of =COUNTIF(DATA::A,A2) you might have something like =COUNTIF(DATA::B,C2).

SG

Hello

If I understand it correctly, you might try something like the following table.

A:J are sample values, L:P are retieved mode values and Q:AF are auxiliary columns.

Table 1 (excerpt) A1 a1 A2 =CHAR(RANDBETWEEN(65,69)) B1 a2 B2 =CHAR(RANDBETWEEN(65,69)) C1 a3 C2 =CHAR(RANDBETWEEN(65,69)) D1 a4 D2 =CHAR(RANDBETWEEN(65,69)) E1 a5 E2 =CHAR(RANDBETWEEN(65,69)) F1 a6 F2 =CHAR(RANDBETWEEN(65,69)) G1 a7 G2 =CHAR(RANDBETWEEN(65,69)) H1 a8 H2 =CHAR(RANDBETWEEN(65,69)) I1 a9 I2 =CHAR(RANDBETWEEN(65,69)) J1 a10 J2 =CHAR(RANDBETWEEN(65,69)) L1 m1 L2 =IF(LEN(Q2)>0,INDEX($A2:$J2,1,Q2),"") M1 m2 M2 =IF(LEN(R2)>0,INDEX($A2:$J2,1,R2),"") N1 m3 N2 =IF(LEN(S2)>0,INDEX($A2:$J2,1,S2),"") O1 m4 O2 =IF(LEN(T2)>0,INDEX($A2:$J2,1,T2),"") P1 m5 P2 =IF(LEN(U2)>0,INDEX($A2:$J2,1,U2),"") Q1 i1 Q2 =MATCH($V2,$W2:$AF2,0) R1 i2 R2 =IFERROR(MATCH($V2,OFFSET($W2,0,Q2,1,COLUMNS($W2:$AF2)-Q2),0)+Q2,"") S1 i3 S2 =IFERROR(MATCH($V2,OFFSET($W2,0,R2,1,COLUMNS($W2:$AF2)-R2),0)+R2,"") T1 i4 T2 =IFERROR(MATCH($V2,OFFSET($W2,0,S2,1,COLUMNS($W2:$AF2)-S2),0)+S2,"") U1 i5 U2 =IFERROR(MATCH($V2,OFFSET($W2,0,T2,1,COLUMNS($W2:$AF2)-T2),0)+T2,"") V1 n V2 =MAX(W2:AF2) W1 n1 W2 =COUNTIF(A2:$J2,A2) X1 n2 X2 =COUNTIF(B2:$J2,B2) Y1 n3 Y2 =COUNTIF(C2:$J2,C2) Z1 n4 Z2 =COUNTIF(D2:$J2,D2) AA1 n5 AA2 =COUNTIF(E2:$J2,E2) AB1 n6 AB2 =COUNTIF(F2:$J2,F2) AC1 n7 AC2 =COUNTIF(G2:$J2,G2) AD1 n8 AD2 =COUNTIF(H2:$J2,H2) AE1 n9 AE2 =COUNTIF(I2:$J2,I2) AF1 n10 AF2 =COUNTIF(J2:$J2,J2)

Notes.

Columns A:J are sample values.

Column V calculates the max value in W:AF, which is mode frequency in A:J.

Columns Q:U calculate the indices of mode values for A:J.

Columns L:P retrieve the mode values using the indices in Q:U.

Formula in W2 can be filled down and right across W2:AF7.

Formula in V2 can be filled down.

Formula in Q2 can be filled down.

Formula in R2 can be filled down and right across R2:U7.

Formula in L2 can be filled down and right across L2:P7.

Table is built with Numbers v2.

Hope this may help,

H