
1. Re: Find the mode (most common value)
Patrick A Van Der Hyde Dec 7, 2018 6:20 AM (in response to Alex Martino)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

excel_pv.twbx 9.8 KB
