Average of Last 20 column values, only Positives / Negatives
Average of last 20 positive values (zero and above) in column A
Cell G4 formula: Average of last 20 positive values (zero and above) in column A.
———————————————————————————————–
Cell G4 formula: Average of last 20 positive values (zero and above) in column A.
Cell G4 formula:
=AVERAGE(IF( ISNUMBER( MATCH(ROW( A2:A50)- ROW(A2)+1, LARGE(( A2:A50>=0)*( A2:A50<>””)*( ROW(A2:A50)- ROW(A2)+ 1), ROW( 1:20)), 0)), A2:A50))
Explanation – Cell G4 formula (Array Formula – Ctrl+Shift+Enter):
1) LARGE((A2:A50>=0)*( A2:A50 <>””)*(ROW( A2:A50)- ROW(A2)+1), ROW(1:20)): returns an array of the 20 largest row numbers of non-blank cells whose values are equal to or greater than zero – {48; 45;40; 39; 37;34; 33; 31;30; 29; 27;23; 19; 18;17; 16; 13;12; 11;10}. This is the lookup_array argument of the MATCH function.
2) ROW(A2:A50)- ROW(A2)+1: returns an array of row numbers starting from 1 to 49 – {1; 2;3; 4;5; 6;7; 8;…; 46;47; 48;49}. This is the lookup_value argument of the MATCH function, and the row numbers of 1) above are matched with these and for the matched rows (ie. where the Match function returns a number) the column A cell values are returned. Using the AVERAGE function returns the Average of these last 20 values >= 0. Using the AVERAGE function returns the Average of these last 20 values which are equal to or greater than zero.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
Cell F4 formula: Average of last 20 positive values (zero and above) in column A. This is similar to cell G4 formula illustrated above, without using the MATCH / ISNUMBER functions. Array formula (Ctrl+Shift+Enter).
Cell G7 formula: Average of last 20 positive values (above zero only) in column A. Uses the LARGE and AVERAGE functions with MATCH / ISNUMBER functions. Array formula (Ctrl+Shift+Enter).
Cell F7 formula: Average of last 20 positive values (above zero only) in column A. This is similar to cell G7 formula above, without using the MATCH / ISNUMBER functions. Array formula (Ctrl+Shift+Enter).
Cell G10 formula: Average of last 20 negative values (below zero) in column A. Uses the LARGE and AVERAGE functions with MATCH / ISNUMBER functions. Array formula (Ctrl+Shift+Enter).
Cell F10 formula: Average of last 20 negative values (below zero) in column A. This is similar to cell G10 formula above, without using the MATCH / ISNUMBER functions. Array formula (Ctrl+Shift+Enter).