User Rating: 5 / 5     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).