Average excluding zero's within an IF(ISERROR) statement

[scottatah]

I'm currently using the following formula:
=IF(ISERROR(AVERAGE(IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136)),"",AVERAGE(IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136))

I've discovered that I now need it to exclude zero's from the average, if they're present, however, I'm not sure how to do so. I've used the =AVERAGEIF function elsewhere with ">0" to do this, but I can't figure out how to tie it in.

The cells in the above formula are all populated via a macro that pulls cells from another daily spreadsheet. While I could likely have these zero's renamed to null or n/a i'd rather leave the zero's and just find a way to exclude them.

Working on Excel 2010.

 

[Aladin Akyurek]

I'm currently using the following formula:
=IF(ISERROR(AVERAGE(IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136)),"",AVERAGE(IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136))

I've discovered that I now need it to exclude zero's from the average, if they're present, however, I'm not sure how to do so. I've used the =AVERAGEIF function elsewhere with ">0" to do this, but I can't figure out how to tie it in.

The cells in the above formula are all populated via a macro that pulls cells from another daily spreadsheet. While I could likely have these zero's renamed to null or n/a i'd rather leave the zero's and just find a way to exclude them.

Working on Excel 2010.

Assumimg that there are no negative numbers in the relevant cells, try...

=SUM(IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136)/INDEX(FREQUENCY((IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136),0),2)

Also, instead of invoking ISERROR...

=SUM(IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136)/MAX(1,INDEX(FREQUENCY((IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136),0),2))

You could name the collection of cells something like NonContiguousRange and use this name in the formula.

 

[scottatah]

Wow you guys are always so quick on the replies. I love it! The latter of your two suggestions works better for my case however, it does cause another issue. I have two questions.

Cell Q136: =SUM(IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136)/MAX(1,INDEX(FREQUENCY((IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136),0),2))

1) Can you give me a bit of a plain english understanding of what the above means. I've looked up the functions individually and hav ea moderate understanding of them, but putting them all together like this doesn't entirely compute. I've also used the "Evaluate Formula" function to walk me through and I understand the outcome, just confused as to exactly how this got us there.

2) A better understanding of the above may make this easier for me, but I'd like this formula to leave the cell blank if the collection of cells in question hasn't yet been filled in yet.

This is necessary for another formula which averages the last 5 entries in this column Q using:
=AVERAGEIF((OFFSET(Q8,COUNT(Q8:Q999)-5,0,5,1)),">0")

With your above suggested formula, any future dates for which I do not have data now contain zero's which throws off my count and thus that formula. I'm not sure if it's smarter to edit one formula instead of another or not, but I'm open to advice/suggestions.

Much appreciated!

 

[Aladin Akyurek]

Wow you guys are always so quick on the replies. I love it! The latter of your two suggestions works better for my case however, it does cause another issue. I have two questions.

Cell Q136: =SUM(IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136)/MAX(1,INDEX(FREQUENCY((IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136),0),2))

1) Can you give me a bit of a plain english understanding of what the above means. I've looked up the functions individually and hav ea moderate understanding of them, but putting them all together like this doesn't entirely compute. I've also used the "Evaluate Formula" function to walk me through and I understand the outcome, just confused as to exactly how this got us there.

The formula is a re-write of average in terms of (a) sum and (b) count. The former is done with SUM, the latter with FREQUENCY.

FREQUENCY creates a two-value array: Suppose we have...

=SUM(G2,J2,L2)/MAX(1,INDEX(FREQUENCY((G2,J2,L2),0),2))

with G2 = 2, J2 = 4, and L2 = 0.

After evaluating the SUM bit and the FREQUENCY bit, we'd get:

=6/MAX(1,INDEX({1;2},2))

{1,2} means 1 value (that of L2) populates the 0 bin. All other values (those of G2 and J2, gives a count of 2 above the 0 bin. Since we are interested in the count of the values above the 0 bin, we feed the array to INDEX, asking it to return the 2nd numeric item from that array:

INDEX({1;2},2) which yields 2. Continuing with the MAX bit, we have:

MAX(1,2) which yields 2. Then:

6/2 gives the desired average.

2) A better understanding of the above may make this easier for me, but I'd like this formula to leave the cell blank if the collection of cells in question hasn't yet been filled in yet.

Try...

=IFERROR(SUM(IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136)/INDEX(FREQUENCY((IZ136,IF136,HL136,GR136,FX136,FD136,EK136,DP136,CV136,CB136,BH136,AN136),0),2),"")

This is necessary for another formula which averages the last 5 entries in this column Q using:
=AVERAGEIF((OFFSET(Q8,COUNT(Q8:Q999)-5,0,5,1)),">0")

With your above suggested formula, any future dates for which I do not have data now contain zero's which throws off my count and thus that formula. I'm not sure if it's smarter to edit one formula instead of another or not, but I'm open to advice/suggestions.

Much appreciated!

Are you calculating an average of averages?