Excel average excluding high/ low numbers скачать в хорошем качестве

Excel average excluding high/ low numbers

00:00 Average versus Average excluding extreme data points 00:20 Use TRIMMEAN to exclude outlying numbers 01:02 Understand TRIMMEAN percentages and how many numbers it cuts off For images see blog post : https://www.auditexcel.co.za/blog/ave.... Your average not making sense? Perhaps you need to average excluding outliers in MS Excel to get a more realistic average. Averages where outliers give an unrealistic average As shown below the average is shown as 2.89, but visually we can see that the 65 is throwing out an unrealistic average. Just looking at the numbers the average should be less then 2 and probably significantly less. In Excel a way around this is to use the TRIMMEAN function Function for average excluding outliers in MS Excel So below we have used the TRIMMEAN function in cell B35. As shown the function just needs to know where to look (B2 to B29) and what percentage of the outlier values to exclude (we have referenced to cell D35 to show the logic). The result of this average is 0.61 instead of the previous average of 2.89 which makes more sense. Understanding the % in TRIMMEAN The only real complexity in TRIMMEAN is what the percentage means. In its simplest it is how many values to leave out, evenly split from the top and the bottom of the value range. But there are some things to realise about this notably that the % results in a number of numbers to exclude, BUT this is rounded DOWN to the nearest multiple of 2! So below, when we use 10% (cell D35), this results in a 2.8 (10% of the 28 values in B2 to B29). Excel cannot use 2.8 so it rounds DOWN to the nearest multiple of 2 (cell G35). It now knows to exclude 2 items, the biggest value and the smallest value which is the 65 and any one of the 0’s. Depending on the percentage used the number of values excluded from the top and bottom changes BUT always in multiples of 2’s and always rounding DOWN.