How can I average non-adjacent cells in excel, excluding zero?

Asked by Haleth (18947

) April 14th, 2010

I’m working on a spreadsheet in Excel 2007 that will record daily information. There are two columns of figures for each day of the week. I want to average columns (B,D,F,H,J,L,N) at the end of the week. I’ve googled this and tried a few things already.

I used the formula =SUM(B2,D2,F2,H2,J2,L2,N2)/COUNTIF(B2:N2,”>0”), and got an error message- something about there being too many cells?

I tried an array formula, =AVERAGE(IF(B2:N2=0,FALSE,B2:N2)), which wasn’t right for this because it included all the cells in the range. I only want these alternating cells.

The reason for excluding zeros is that I will be entering this information daily throughout the week. I want my averages to be as accurate on Wendsday, when the chart is half full, as they are on Sunday.

Observing members: 0

Composing members: 0

3 Answers

I copied your formula and it’s working for me in Excel 2007,
=SUM(B2,D2,F2,H2,J2,L2,N2)/COUNTIF(B2:N2,”>0”)

jaytkay (25810

)“Great Answer” (0

) Flag as…

Here’s an array formula that should do what you want:

= IF( NOT( ISBLANK( B2:N2)), AVERAGE( B2, D2, F2, H2, J2, L2, N2), NA())

If you don’t mind the #DIV/0 errors for the weeks that have no data at all yet, the simple:
=AVERAGE( B2, D2, F2, H2, J2, L2, N2) will only average cells that have entries. (So it WILL average a zero-value, if zero has been explicitly entered.)

CyanoticWasp (20271

)“Great Answer” (0

) Flag as…

@CyanoticWasp

Thanks! I’ll try both of those. The second one might be what I’m looking for, because I think I will need to differentiate between zeros and blank cells. :)

Haleth (18947

)“Great Answer” (0

) Flag as…