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.
———————————————————————————————–
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.