1 Reply Latest reply on Dec 7, 2018 6:20 AM by Patrick Van Der Hyde

    Find the mode (most common value)

    Alex Martino

      Hello. I have a column with averages by order ID. How do I find the most common average and return that value as it's own column of values? Spreadsheet attached.

       

      In this case, the new column would have all "2.0" (since 2.0 is the most common average). Thanks!

        • 1. Re: Find the mode (most common value)
          Patrick Van Der Hyde

          Alex,

           

          See the attached updated workbook.  This request can be done using Table Calculations.

           

          First you need to use Index() and set this function to utilize a Discrete value of the Avg(Price) as a value in the view.  You will see that in the attached workbook.  Then,  the index() function will be able to count the number of each avg(price) value that exist in the view.  Note that the Index() function should also be set to sort the Avg(Price) value in the calculation.

           

          Next we find if the count of the values is the highest one and then set it to true.  In this case, that value is the number 2 and therefore the the one row with index value of 2 is returned as True.

           

          Finally, we take the value for the True condition above and return the value for avg(Price) and then use window_avg() to get this value for all rows.  All of the other rows will be equal to null so 2 is the right output.

           

          This logic works as long as there is one value that appears most but it will fall apart if many values are tied for most frequent value.

           

          I hope this helps.

           

          Patrick