HOME > SOFTWARE > EXCEL
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.
Note: Commissions may be earned from the links above.
This page contains references to products from one or more of our advertisers. We may receive compensation when you click on links to those products. For an explanation of our advertising policy, please visit this page.
Related posts:
How to get the average of the last 5 values in Excel
https://w3ask.com/average-last-5-values-excel/
https://w3ask.com/average-last-5-values-excel/
How to obtain the average of the last x cells in the Excel software and thus see if there is an evolution with our data compared to other averages, ...
How to ignore #DIV/0! and zeros in Excel with AVERAGE()
https://w3ask.com/ignore-div-zeros-excel-average/
https://w3ask.com/ignore-div-zeros-excel-average/
The AVERAGE function has a problem generating a result from a column that presents errors, such as the famous #DIV/0!, or zero values. How can ...
Excel macro to insert a picture with Redime
https://w3ask.com/excel-macro-insert-picture-redime/
https://w3ask.com/excel-macro-insert-picture-redime/
You want to save a macro that will allow you to insert a picture (located in a directory of your hard drive disk) in an active cell where you ...
How to add lines with a VBA code in Excel
https://w3ask.com/how-add-lines-vba-code-excel/
https://w3ask.com/how-add-lines-vba-code-excel/
If you want to automatically add line in your Excel sheet, you can use a VBA code. The following example will add lines after the N column in ...
How to convert a number in time with Excel ?
https://w3ask.com/convert-number-in-time-excel/
https://w3ask.com/convert-number-in-time-excel/
When we enter a date with points, is there a possibility to convert it automatically into a date with slashes, so that it could be taken into ...