How to have a better average in Excel?

With the functions of average in Excel, we quickly notice their limitation. For example AVERAGE() gives an overall average that can be highly distorted if the high part is very high or the lower part is too low; the result in an average that does not reflect the biggest part of the numbers. While MEDIAN() cuts the data into two groups of the same size and displays the value that allows the cutting; here too, the result does not represent the largest part of the values, contrary to what many commentators on news sites say when we talk about statistical data.

So what alternative to the AVERAGE() and MEDIAN() functions?

The solution is to use TRIMMEAN() which removes the most extreme elements, whether in the lower part or the upper part of the table:

=TRIMMEAN(I2:I297;I305)

I2: I297 is the column where your data is located. And I305 refers to the percentage of the column you want to remove from your calculation, ie the extremes (eg 0.05, ie 5%). In the end the result is much more representative than AVERAGE or MEDIAN. This is exactly the type of average that statistical agencies such as Federal Reserve should use to talk about average income, OECD, WHO, etc.


Related Post

Share

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz