Home / Questions / 289190
Accepted
fedorqui
Asked: 2020-08-30 11:23:38 +0800 CST 2020-08-30 11:23:38 +0800 CST

How do I see what the most common value (mode) of a column is?

  • 772

Suppose I have a Google Spreadsheet with this data:

 A
----
foo
bar
bar
pim
pam
foo
foo

I am interested in knowing which is the value that appears more times. In this case, I would like to get fooas it appears three times.

Searching, I found that in How to output the most common value and the number of occurrences of that value in spreadsheet? they supposedly solve this case, but even the initial step doesn't work for me. Indeed, if I do:

=COUNTIF(A1:A7;A1:A7)

It returns 0 when I put it on the right margin (B8, one row below the last item in column A).

More generally, my interest is to obtain a parallel table with a summary of the type:

valor     ocurrencias
---------------------
foo       3
bar       2
pam       1
pim       1
google-hoja-de-cálculo

3 Answers

  1. Best Answer

    To get the count of the most common value use

    =ArrayFormula(MAX(COUNTIF(A1:A7;A1:A7))

    A way to get the frequency table sorted in descending order is mentioned in a previous answer . Here are some other ways:

    Dynamic table

    A very cool feature of spreadsheets is pivot tables , but this requires the data to have a header. You would add the values ​​in the rows section and also in the values ​​section using the CONTARA( COUNTA) function, then sort the rows in descending order based on CONTARA.

    Statistical functions

    There are functions MODA( MODE), MODA.UNO( MODE.SNGL) and MODA.VARIOS( MODE.MULT) but these require that the set of values ​​be numbers. You could use these functions by "hardcoding" the type values ​​by TEXTOassigning them a type value NÚMERO.

    Matrices / ArrayFormula

    Variant of the solution provided in DjCrazi's answer , instead of using two formulas, only one is used, taking advantage of the use of arrays feature in Google spreadsheet and the ArrayFormula.

    =ArrayFormula(UNIQUE({A1:A7,COUNTIF(A1:A7,A1:A7)}))

    Its parts are explained below.

    Custom Functions, Plugins, Script

    It is also possible to use JavaScriptvia Google Apps Scripteither directly to create a custom function or script or indirectly via a plugin. To deal with this question of a formula I will not go into details in this answer.

    Below is an explanation of what is going on with =COUNTIF(A1:A7;A1:A7)and how the proposed array formula/ArrayFormula was arrived at.

    Don't let the cunic panda!

    In a new spreadsheet placing the data in A1:A7and the formula =COUNTIF(A1:A7;A1:A7)in the cell B1returns the value of 3, but if the data is moved, say one cell down, and the formula is left in its original position, it doesn't work.

         

    Chanfle

    It turns out that we have been "victims" of one of the quirks of Google Spreadsheets, specifically the way it handles arrays. Putting the formula referred to in the question inside the function ArrayFormulareturns an array of values

    I suspected them from the start

    To get the frequency table in descending order using a single formula we could use=ArrayFormula(UNIQUE({A1:A7,COUNTIF(A1:A7,A1:A7)}))

    The good thing is that it even works if we move the data of its position

    rechanfle

    • {A1:A7,COUNTIF(A1:A7,A1:A7)}This part returns a matrix of six rows by two columns. The first column includes the values ​​to analyze and the second column the number of frequencies. NOTE: If your spreadsheet uses a comma as a decimal separator, use {A1:A7\COUNTIF(A1:A7;A1:A7)}.
    • UNIQUE(...)Returns an array including unique rows.
    • ArrayFormula(...)Displays the array to the height and width required by the array resulting from its argument.

    recontrachanfle

    Another alternative is to create a custom function using Google Apps Script or a plugin where someone else has already created this function for you. Please note that very soon plugins will no longer be available in the Chrome Webstore but will be available in the Works with Sheets section of the G Suite Marketplace and in Sheet templates that have a plugin linked if the plugin developer does so. what do you have to do for it.

    Follow me the good ones

    Today the simplest and probably most convenient solution in a wide variety of circumstances is to use pivot tables.

    Is it normal for it to =COUNTIF(A1:A7;A1:A7)return 0?

    Returns 0 if the formula is in any other row that does not span the range with the values, but if it is in a row that spans the values ​​it returns the number corresponding to the relative row, for example,

    If the range is A1:A7 and the formula is placed in

    • B8, returns 0
    • B1(foo), returns 3
    • B2(bar), returns 2
    • 11

  2. For this you can use two functions: uniqueandcontar.si

    enter image description here

    In cell B1 , with =UNIQUE(A1:A7)you return the list with the values ​​without repeating. Then, in cell C1 with =CONTAR.SI(A$1:A$7;B1)samples the number of times that are repeated in the first column.

    • 7

  3. I don't know if this is what you are looking for but I got the following formula:

    '=CONTAR.SI(A2:A7,C2)'

    enter image description here

    • 3