Print

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

AVERAGE of Non Contiguous Cells which are Evenly Distributed - Odd or Even rows

 


 

AVERAGE of Non Contiguous Cells which are Evenly Distributed - Average of Odd / Even Numbered column A rows

  

Cell C12 formula: Average Odd numbered rows in column A, starting from cell A2 (ie. cells A3, A5, A7, A9, A11, A13, A15, A17, A19) - ignore blank cells.   

 

----------------------------------------------------------------------------------------------- 

Average Non-Contiguous Cells, Evenly Distributed  

 

Cell C12 formula: Average Odd numbered rows in column A, starting from cell A2 (ie. cells A3, A5, A7, A9, A11, A13, A15, A17, A19) - ignore blank cells.  

  

Cell C12 formula: 

  

=AVERAGE(IF( A2:A20<>"", IF(MOD( ROW(A2:A20), 2)=1, A2:A20)))   

   

Explanation - Cell C12 formula (Array Formula - Ctrl+Shift+Enter):

 

1) MOD(ROW(A2:A20), 2): returns an array of 1s and zeros - {0;1; 0;1; 0;1; 0;1; 0;1; 0;1 ;0; 1;0; 1;0; 1;0}. Using MOD function, for row numbers and the divisor of 2, returns zero for even numbers and 1 for odd numbers.

 

2) MOD(ROW(A2:A20),2)=1: Equating the above array with 1 returns an array of TRUE and FALSE values where TRUE is returned for 1s which indicate odd numbered rows - {FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}.

 

3) IF(A2:A20<>"", IF(MOD( ROW(A2:A20), 2)=1, A2:A20)): Using the IF function with the above array considers non-blank column A cells (using the condition A2:A20<>"") which correspond to TRUE and returns column A values from these non-blank odd numbered rows ie. {FALSE;3; FALSE; 5; FALSE;7; FALSE;9; FALSE; FALSE; FALSE; FALSE; FALSE; 11; FALSE;13; FALSE; FALSE; FALSE} - where blank cells in odd numbered rows and cells in even numbered rows both return FALSE and are not considered. Using the AVERAGE function will consider all the non-blank cells from odd numbered rows. 

  

 

-----------------------------------------------------------------------------

 

Related Formulas - illustrated in the Ebook of Excel Formulas.

 

 

Other formulas:

 

AVERAGE of Odd / Even Numbered column A rows, or Average of Alternate column A rows.

 

Cell C5 formula: Average Odd numbered rows in column A, starting from cell A2 (ie. cells A3, A5, A7, A9, A11, A13, A15, A17, A19) - consider blank cells as Zero. Array formula (Ctrl+Shift+Enter).

 

Cell C19 formula: Average Odd numbered rows in column A, starting from cell A2 (ie. cells A3, A5, A7, A9, A11, A13, A15, A17, A19) - ignore blank cells. Instead of using MOD this formula uses hard-coded row numbers to identify odd numbered rows - Array formula (Ctrl+Shift+Enter).

 

Cell C22 formula: Average Even numbered rows in column A, starting from cell A2 (ie. cells A2, A4, A6, A8, A10, A12, A14, A16, A18, A20) - consider blank cells as Zero. Array formula (Ctrl+Shift+Enter).  

 

Cell C29 formula: Average Even numbered rows in column A, starting from cell A2 (ie. cells A2, A4, A6, A8, A10, A12, A14, A16, A18, A20) - ignores blank cells. Array formula (Ctrl+Shift+Enter).

 

Cell C36 formula: Average Alternate Cells in column A, starting from cell A2 (ie. cells A2, A4, A6, A8, A10, A12, A14, A16, A18, A20) - consider blank cells as Zero. Array formula (Ctrl+Shift+Enter).

 

Cell C46 formula: Average Alternate Cells in column A, starting from cell A2 (ie. cells A2, A4, A6, A8, A10, A12, A14, A16, A18, A20) - ignore blank cells. Array formula (Ctrl+Shift+Enter).

 

 

AVERAGE of every 3rd column A cell, or each block of 3 column A cells.

 

Cell C57 formula: Average of every 3rd column A cell starting from A2, ie skip 2 cells at a time (cells A2, A5, A8, A11, A14, A17, A20) - ignore blank cells. Array formula (Ctrl+Shift+Enter).

 

Cell C66 formula: Average of every 3rd column A cell starting from A2, ie skip 2 cells at a time (cells A2, A5, A8, A11, A14, A17, A20) - ignore blank cells. Normal, Non-Array formula. 

 

Cell C77 formula: Return Average of each block of 3 column A cells (ie cells A2:A4, A5:A7, A8:A10, A11:A13, A14:A16, A17:A19), in each successive cell  - ignore blank cells. Enter Formula in start cell & copy down. Normal, Non-Array formula.