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?

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

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

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

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).

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

Brian

Edit yep it does.

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

It still counts the cell in 2002.

Brian