Average of first 10 values in a column, Ignore Error Values, Exclude / Include Zeros & Blank Cells
Average of first 10 values in a column, Ignore Error Values
Cell C8 formula: Average of the first 10 values in column A, ignore errors, includes zeros, excludes blank cells.
———————————————————————————————–
Cell C8 formula: Average of the first 10 values in column A, ignore errors, includes zeros, excludes blank cells.
Cell C8 formula:
=SUM(IFERROR( INDEX(A2:A30, SMALL( IFERROR(IF( A2:A30<>””, ROW(A2:A30)- ROW(A2)+1), “”), 10)):A2, “”))/10
Explanation – Cell C8 formula (Array Formula – Ctrl+Shift+Enter):
1) IFERROR(IF( A2:A30<>””, ROW(A2:A30)- ROW(A2)+1),””): returns an array of positions / row numbers – {“”;2; 3; “”;5;6; 7; FALSE;9; 10; 11;12;””; 14;15; 16; 17; 18; FALSE;””; 21;FALSE; FALSE;…} – for column A cells including cells with zeros but ignoring blank cells & error values. Using the SMALL function will return the row number of the 10th smallest cell for these values, which is 14.
2) IFERROR(INDEX( A2:A30, SMALL( IFERROR(IF( A2:A30<>””, ROW(A2:A30)- ROW(A2)+1), “”), 10)):A2,””): returns values from column A – {“”;0;3;””; 11;19; 8; 0; 20;28; 25;0; “”;21} – starting from cell A2 till the 10th smallest row number as above (ie. 14 – cell A15). Essentially these are the 1st 10 values in column A , including cells with zeros & skipping blank cells & error values. By using INDEX function, zeros are ALSO displayed for blank cells BUT for calculating the 1st 10 cells these zeros for blank cells are ignored so that there will be a total of 10 column A values only including actual zero values. Using SUM for these 10 values (extra zeros for blank cells does not affect the SUM) & dividing by 10 returns the AVERAGE of the 1st 10 values in column A, including cells with zeros & skipping blank cells & error values.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
Cell C3 formula: Average of the first 10 values in column A, excludes blank cells, zeros & errors. Array formula (Ctrl+Shift+Enter).
Cell C5 formula: Average of the first 10 values in column A, ignore errors, includes zeros and also considers blank cells as zeros. Array formula (Ctrl+Shift+Enter).
Cell C6 formula: Average of the first 10 values in column A, ignore errors, includes zeros and also considers blank cells as zeros. Array formula (Ctrl+Shift+Enter).
Cell C10 formula: This formula returns the Average of values “A6:A13”. Note: Excel Average Function omits blank cells and includes all numbers including zeros. Normal, Non-Array formula.
Cell C11 formula: Average of column A values, ignore errors – this omits blank cells and considers zero values. Array formula (Ctrl+Shift+Enter).