AVERAGE of Non Contiguous Cells which are Unevenly Distributed
AVERAGE of Non Contiguous Cells which are Unevenly Distributed
Cell D9 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10) having values greater than zero.
———————————————————————————————–
Cell D9 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10) having values greater than zero.
Non-Contiguous Cells with uneven intervals (A2, A4, A5, A7,A8, A10) of Column A are considered – cells may have +ive / -ive values, and blank cells are to be ignored.
Cell D9 formula:
=AVERAGE(IF( SUBTOTAL(9, OFFSET(A2, {0,2,3,5,6,8},))>0, SUBTOTAL(9, OFFSET(A2, {0,2,3,5,6,8},))))
Explanation – Cell D9 formula (Array Formula – Ctrl+Shift+Enter):
1) SUBTOTAL(9, OFFSET(A2, {0,2,3, 5,6,8},)): Using the OFFSET function with SUBTOTAL, returns the values from each of the 6 non-contiguous cells – {108.51, 108.13, 101, -17.72,0,0} – where zero is returned for cells containing zero and also for blank cells. This offsets 6 rows {0,2,3, 5,6,8} from cell A2 to return each of the 6 non-contiguous cells.
2) IF(SUBTOTAL(9, OFFSET(A2, {0,2,3,5,6,8}, ))>0, SUBTOTAL(9, OFFSET(A2, {0,2,3, 5,6,8}, ))): The IF statement is used to determine cells having values > 0 and returns the array {108.51, 108.13, 101, FALSE, FALSE, FALSE}, where FALSE is returned for values less than or equal to zero, and then using the AVERAGE function returns the average of the values which are greater than zero ie. 105.880. Using OFFSET, in combination with SUBTOTAL, ensures that each cell gets evaluated separately and then using SUBTOTAL (Offset delivers each cell as a separate range to SUBTOTAL) returns the value in each of these cells.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
AVERAGE 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10) having values greater than zero.
Cell D5 formula: This performs the same as cell D9 formula ie. Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10) having values greater than zero. Uses the CHOOSE function – Array formula (Ctrl+Shift+Enter).
Cell D14 formula: This performs the same as cell D9 formula ie. Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10) having values greater than zero. Uses the SUMIF / COUNTIF functions with INDIRECT (references the range B2:B5 which contain each cell / sub-range reference) – Normal Non-Array formula.
Cell D22 formula: This performs the same as cell D9 formula ie. Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10) having values greater than zero. Uses the SUMIF / COUNTIF functions with INDIRECT (references each individual cell separately). Normal Non-Array formula.
AVERAGE 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), consider Blank Cells as zero.
Cell D26 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), consider Blank Cells as zero. Uses the CHOOSE function – Normal, Non-Array formula.
Cell D29 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), consider Blank Cells as zero. Uses the OFFSET function with SUBTOTAL – Normal, Non-Array formula.
Cell D33 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), consider Blank Cells as zero. Uses the SUMIF / COUNTIF functions with INDIRECT (references the range B2:B5 which contain each cell / sub-range reference) – Normal, Non-Array formula.
Cell D44 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), consider Blank Cells as zero. Uses the SUMIF / COUNTIF functions with INDIRECT (references each individual cell separately) – Normal, Non-Array formula.
AVERAGE 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), ignore Both Zeros or Blanks.
Cell D48 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), ignore Both Zeros or Blanks. Uses the CHOOSE function – Array formula (Ctrl+Shift+Enter).
Cell D52 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), ignore Both Zeros or Blanks. Uses the OFFSET function with SUBTOTAL – Array formula (Ctrl+Shift+Enter).
Cell D58 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), ignore Both Zeros or Blanks. Uses the SUMIF / COUNTIF functions with INDIRECT (references the range B2:B5 which contain each cell / sub-range reference) – Normal, Non-Array formula.
Cell D69 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), ignore Both Zeros or Blanks. Uses the SUMIF / COUNTIF functions with INDIRECT (references each individual cell separately) – Normal, Non-Array formula.
AVERAGE 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), ignore Blank Cells.
Cell D73 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), ignore Blank cells. The 6 non-contiguous cells are combined into a Named Range – Normal, Non-Array formula.
Cell D74 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), ignore Blank cells. Uses the OFFSET function with SUBTOTAL – Normal, Non-Array formula.
Cell D80 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), ignore Blank cells. Uses the SUMIF / COUNTIF functions with INDIRECT (references the range B2:B5 which contain each cell / sub-range reference) – Normal, Non-Array formula.
Cell D87 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10), ignore Blank cells. Uses the SUMIF / COUNTIF functions with INDIRECT (references each individual cell separately) – Normal, Non-Array formula.