AVERAGEIF over non-contiguous range...there has to be a better way

[ZenZilla]

I'm trying to average three non-contiguous cells that contain referential formulas, based on the criteria that the values are >0 (I want to ignore 0 values in the average). Everything I can think of either doesn't work or will be stupidly complex. There must be a simple way to use criteria formulas (SUMIF, COUNTIF, AVERAGEIF, etc...) with non-contiguous ranges.

Such a simple problem...Thank you for any assistance you can provide.

 

[Jonmo1]

Which version of Excel are you using?
Is it really just 3 single cells ? Or will you be expanding to 'several' or 'many' cells ?
Is there any pattern to the cells that could be used, like every 3rd cell?
Or maybe an adjescent column/row has some text flag, like average the cells in column B if the cell in column A has "x" in it?

 

[ZenZilla]

I'm using Excel 2010.

And yes it really is just three single cells...well per formula. Once I find a solution I will be using it for 10 different averages each of which will be for three cells. Those cells being J3,W3, and AC3 for the first set. Same columns for rows 3-12, but each row will be independent as I said above.

 

[Jonmo1]

Unfortunately it is going to be complex to use non contiguouse ranges.

I have this for starters.
For just 3 cells, try this array formula entered with CTRL + SHIFT + ENTER

=AVERAGE(IF(CHOOSE({1;2;3},J3,W3,AC3)>0,CHOOSE({1;2;3},J3,W3,AC3)))


But is there any other pattern or flag like I mentioned in previous post?
What is in the between cells? K3 to V3, X3 to AB3 ?
If those cells are NON Numeric, then you can just do
=AVERAGEIF(J3:AC3,">0")
Blanks and TEXT values in the 'between' cells will be ignored.

 

[ZenZilla]

Ah, that was a solution I had not thought of! It seems to be working without issue.

There is no other pattern or flag, and the cells in-between are all numeric values.

Thank you so much. Still hard to believe there isn't a built in way to do this

 

[Jonmo1]

You're welcome.

There is a built in way to average non contiguouse cells.
=AVERAGE(J3,W3,AC3)

It's the added criteria of >0 that is making it complicated.


Are the cells in J3 W3 AC3 the result of a Formula?
Perhaps it might be easier to make those formulas return "" instead of 0 ?

 

[ZenZilla]

Yes, they are the result of this formula:

=INDEX('Previous Year'!D:D,MATCH(1,($A4='Previous Year'!$A:$A)*(Summary!$T$2='Previous Year'!$B:$B),0))

J3 example above.

 

[Jonmo1]

You could replace that with
=IFERROR(1/(1/INDEX('Previous Year'!D:D,MATCH(1,($A4='Previous Year'!$A:$A)*(Summary!$T$2='Previous Year'!$B:$B),0))),"")

That would make those cells return "" instead of 0, then plain old average works fine.

 

[XOR LX]

=INDEX('Previous Year'!D:D,MATCH(1,($A4='Previous Year'!$A:$A)*(Summary!$T$2='Previous Year'!$B:$B),0))

Using entire column references within an array formula is not at all a good idea.

Unlike some functions, e.g. COUNTIF(S), SUMIF(S), with which the use of entire column references has virtually no detriment to calculation performance, array-processing functions must calculate over all cells passed to them. And that applies equally to those cells which are beyond the last non-empty cell in the range being passed.

So if, for example, you only have data extending as far as row 1000, then, by using entire column references in such a formula, you are forcing Excel to calculate more than one million cells beyond that which is actually necessary, an astonishing amount for a single formula.

As such, it is important to either choose a suitably low - though sufficient - value for the upper row reference, or else make your ranges dynamic, such that the upper row reference is determined via a function which automatically detect the last-used cell within the column, thus offering maximum efficiency.

Regards

 

[ZenZilla]

You could replace that with
=IFERROR(1/(1/INDEX('Previous Year'!D:D,MATCH(1,($A4='Previous Year'!$A:$A)*(Summary!$T$2='Previous Year'!$B:$B),0))),"")

That would make those cells return "" instead of 0, then plain old average works fine.

This formula works as a replacement, but I'm not sure I understand the logic. Specifically the use of IFERROR and 1/.

@XOR LX Thanks for the tip! I will change them to the proper ranges and hopefully I will see an increase in the calculation speed