AVERAGE of Last 10 values in a Column – Exclude Highest / Lowest
Average last 10 values in a column, exclude the 2 Largest & 2 lowest values
Cell G3 formula: Average last 10 values in a column, exclude the 2 Largest & 2 lowest values and consider net 6 values (skip blanks).
———————————————————————————————–
Cell G3 formula: Average last 10 values in column A, exclude the 2 Largest & 2 lowest values and consider net 6 values (skip blanks).
Cell G3 formula:
=TRIMMEAN(INDEX(A2:A30, LARGE(IF( A2:A30<>””, ROW(A2:A30)- ROW(A2)+1, “”), 10)):A30, 0.4)
Explanation – Cell G3 formula (Array Formula – Ctrl+Shift+Enter):
1) LARGE(IF(A2:A30<>””, ROW(A2:A30)- ROW(A2)+1, “”), 10): this returns the cell position of 13 (ie. cell A14) in column A from where the last 10 non-blank cells start. Starting from the 13th position (ie. 13th non-blank cell starting from cell A2) there would be 10 cells with values.
2) INDEX(A2:A30, LARGE( IF( A2:A30<>””, ROW( A2:A30)- ROW(A2)+1, “”), 10)):A30: this returns all values in the range starting from the 13th position (ie. cell A14) till the last cell of A30 – {1;6;8;5; 4;12;14; 18;0;7;0; 16;0;0; 0;0;0} – the numbers are the 10 values in the last 10 non-blank cells in column A where blank cells also appear as zeros but are not considered in calculation (these are considered blank only). Note that if a non-blank cell has zero then this is also considered as one of the 10 values.
3) Using the TRIMMEAN function calculates the mean taken by excluding a percentage (“percent” argument of the function) of data points from the top and bottom tails of a data set. If “percent” = 0.4, 4 points are trimmed from a data set of 10 points (10 x 0.4): 2 from the top and 2 from the bottom of the set. Hence this excludes the 2 Largest & 2 Smallest values out of the 10 and calculates Average of balance 6 values. Also Note that the fractional number of data points are ignored in the “percent” argument, so that if there are 12 values and “percent” is 0.4, it will still trim or exclude 4 points – 2 Largest & 2 Smallest (12 x 0.4 = 4.8, but rounded down to 4).
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
Cell G5 formula: Average largest 5 out of last 10 values in column A (exclude blanks). Array formula (Ctrl+Shift+Enter).
Cell G7 formula: Average last 10 values in a column excluding blanks – uses the LARGE and INDEX functions with AVERAGE.
Cell G8 formula: SUM last 10 values in a column excluding blanks – uses the LARGE and INDEX functions with SUM.
Cell G9 formula: SUM last 10 values in a column excluding blanks – uses the AGGREGATE and INDEX functions with SUM.
Cell G11 formula: Average all values in a column, excluding the 20% highest & 20% lowest values (excludes blanks) – Out of total 22 values, Excludes 4 Largest & 4 Smallest.