Averaging non-contiguous columns, while excluding certain cells or rows (Excel 2010)

[Jack032]

While it sounds relatively easy, I am experiencing difficultycreating a formula to validate the actual result. Within the range to be calculated, data includes 0’s, nonumbers at all and/or text. For this calculation, I started with =AVERAGEIFS(D4:H48,D4:H48,">0",D4:H48,"<>"""),but noticed it returned an erroneous number. This formula contains the entire datarange, but does not exclude row or cells which are part of the result. Thereforeit was modified to =AVERAGEIFS(D4:H12,D4:H12,">0",D4:H12,"<>"""),AVERAGEIFS(D14:H24,D14:H24,">0",D14:H24,"<>"""),but unable to get it working properly. I determined, the ranges were not equalin size. My next step was to try =SUM(D4:D12,F4:F12,H4:H12)/INDEX(FREQUENCY((D4:D12,F4:F12,H4:H12),0),2),but encountered a separate problem. I was unable to add additional criteria anddeal with the unwanted cells/rows.

The data to be averaged is contained within columns D, F andH, (lines 4 to 49), but must exclude rows 13 (cells D13, F13 and H13), 25, 35, 39,43, 47 and 49.

Any assistance would be greatly appreciated..

 

[Eric W]

Welcome to the Board.

Trying to perform a function on a disjoint range is really difficult. If you don't mind including 0 values, then going back to first principles would work like this:

=AVERAGE(D4:D12,D14:D24,D26:D34,D36:D38,D40:D42,D44:D46,D48,F4:F12,F14:F24,F26:F34,F36:F38,F40:F42,F44:F46,F48,H4:H12,H14:H24,H26:H34,H36:H38,H40:H42,H44:H46,H48)

Long, but very straightforward and easy to understand. The AVERAGE automatically ignores non-numeric data.

If you need to exclude zeros, then maybe:

=AVERAGE(IFERROR([COLOR=#ff0000]1/(1/D4:H48)[/COLOR]*[COLOR=#0000ff](1/MOD(COLUMN(D4:H48)+1,2))[/COLOR]*[COLOR=#008000]1/(1-ISNUMBER(MATCH(ROW(D4:H48),{13;25;35;39;43;47},0)))[/COLOR],""))

confirmed by pressing Control+Shift+Enter. The part in red returns either the value of the cell, or an error if it's zero or non-numeric. The part in blue returns either 1 or an error if the column is odd. The part in green returns either 1 or an error if the row is in the array constant. So multiplying them all together gets you either the value of the cell or an error. The IFERROR changes the errors to "", which AVERAGE will ignore.

But it's complicated. You may be better off with a UDF, or breaking it into a few smaller sub-formulas. Let us know if this works for you.

 

[Jack032]

Eric W,

Great work.. Both formulas worked perfectly, but opted to use the second option due to the possibility of zeros being involved at a later time.

Thank you so much.

Welcome to the Board.

Trying to perform a function on a disjoint range is really difficult. If you don't mind including 0 values, then going back to first principles would work like this:

=AVERAGE(D4:D12,D14:D24,D26:D34,D36:D38,D40:D42,D44:D46,D48,F4:F12,F14:F24,F26:F34,F36:F38,F40:F42,F44:F46,F48,H4:H12,H14:H24,H26:H34,H36:H38,H40:H42,H44:H46,H48)

Long, but very straightforward and easy to understand. The AVERAGE automatically ignores non-numeric data.

If you need to exclude zeros, then maybe:

=AVERAGE(IFERROR([COLOR=#ff0000]1/(1/D4:H48)[/COLOR]*[COLOR=#0000ff](1/MOD(COLUMN(D4:H48)+1,2))[/COLOR]*[COLOR=#008000]1/(1-ISNUMBER(MATCH(ROW(D4:H48),{13;25;35;39;43;47},0)))[/COLOR],""))

confirmed by pressing Control+Shift+Enter. The part in red returns either the value of the cell, or an error if it's zero or non-numeric. The part in blue returns either 1 or an error if the column is odd. The part in green returns either 1 or an error if the row is in the array constant. So multiplying them all together gets you either the value of the cell or an error. The IFERROR changes the errors to "", which AVERAGE will ignore.

But it's complicated. You may be better off with a UDF, or breaking it into a few smaller sub-formulas. Let us know if this works for you.

 

[Eric W]

Glad to help!