Average non-contiguous cells, ignoring zeros

[bobfin]

I have a large spreadsheet where B46 =AVERAGE(G42,L42,Q42). But if any of the values are zero, I want the average to ignore the cell (divide by 2 or 1 instead of 3). There's an Excel help topic on this situation that averages the range A2:A7 with the array formula {=AVERAGE(IF(A2:A7<>0,A2:A7,""))}. How do I write the formula for my non-contiguous cells?

 

[shades]

Howdy. You could use an IF statement combined with COUNTIF and then use the average based on that.
________
Stobart motorsport

 

[Brianwarnock]

I'm not sure how that would work, but you could use Sum and If

average =Sum(g42,l42,q42)/sum(if(g42=0,0,1),if(l42-0,0,1),if(q42=0,0,1))


wouldn't like to write it for many cells.

Brian

 

[lemo]

another possibility - in the excel options there is a check to show zeros as zeros. if you uncheck it, there will be blanks instead. which will solve your problem - but at the expense of those zeros..
l

 

[shades]

Will that eliminate the issue though, because the average is still counting number of cells? (I don't have Excel currently, so can't check).

 

[Brianwarnock]

Rich
I think, well feel pretty sure, that average ignores blanks, but think I will check.

Brian

Edit yep it does.

 

[lemo]

ok, i think there is a problem - when you show blanks instead of zeros (through Excel Options), the ZEROS ARE STILL THERE, they are just hidden. like their font is white.

my apologies. i projected this feature from pivot tables, i guess, where it works just fine..

this is for Excel 2007, btw, you may want to double check how it behaves in the previous version.

l

 

[Brianwarnock]

It still counts the cell in 2002.

Brian