Average of everyother cell (excluding zeros)

[aaverett3]

Hi all,

I current have an Excel doc that I am trying to pull averages from. Every row has data that needs to be averaged but I only need everyother cell. And I cannot have zeros in the average because it will skew the data. Also if the average for a row ends up being 0, I want that average to show up as 0 not #DIV/0!. From Column H to EE I need to average each row individually.
I came up with the below forumula.

=IF(SUM(H1:H1:J1:J1:L1:L1)=0, "0", AVERAGEIF(H1:H1:J1:J1:L1:L1, ">0")

It works but Its not practical because I have to manually enter all of the cells that it pulls from. If anyone could offer any advice or help, it would be much appreciated.

Thanks in advance!

[Kevin UK]

Hi aaverett3

The AVERAGEIF is not available in Excel 2003

[aaverett3]

Sorry! I didn't update that correctly. I have Excel 2010 I believe

[tigeravatar]

aaverett3,

Welcome to the forum!
Give this a try:

Please Login or Register  to view this content.

[aaverett3]

I just tried this and it does not address the issue of using every other cell. I also realized that my formula fails to do so as well

[tigeravatar]

Updated formula:

Please Login or Register  to view this content.

[tigeravatar]

With error checking (backwards compatible):

Please Login or Register  to view this content.

Excel 2007+:

Please Login or Register  to view this content.

[aaverett3]

That works except it still counts the zeros in the averages.

[daddylonglegs]

This array formula will average every other cell from H1, excluding zeroes

=AVERAGE(IF(MOD(COLUMN(H1:EE1)-COLUMN(H1),2)=0,IF(H1:EE1<>0,H1:EE1)))

confirmed with CTRL+SHIFT+ENTER

[aaverett3]

It came back with a #Value! Error. I am trying to work through it but I can't figure out why exactly

[daddylonglegs]

You need to confirm with CTRL+SHIFT+ENTER

Paste the formula in a cell, press F2 key to select formula then hold down CTRL and SHIFT keys while pressing ENTER - if done correctly you'll get curly braces like { and } around the formula in the formula bar.....and a numeric result

[aaverett3]

Is there anyway to permanently confirm the formula? Becuase my coworkers will probably be in this document at some point and I don't want to have to confirm the formula everytime they double clicck on the "average" cell

[aaverett3]

And if not is there another approach I could take?

[daddylonglegs]

If you don't want to use an array formula you can go back to tigeravatar's approach - this version doesn't need CSE but should give you the same result

=SUMPRODUCT(--(MOD(COLUMN(H1:EE1)-COLUMN(H1),2)=0),H1:EE1)/MAX(1,SUMPRODUCT(--(MOD(COLUMN(H1:EE1)-COLUMN(H1),2)=0),--(H1:EE1>0)))

[aaverett3]

Thank you all so much!! This just saved the day for me!

[daddylonglegs]

If you can have some sort of "identifier" to show which columns to average, e.g. an "x" in row 1 indicating which columns to average in row 2 then it can be a lot easier because you can use AVERAGEIFS like this

=IFERROR(AVERAGEIFS(H2:EE2,H$1:EE$1,"x",H2:EE2,">0"),0)