Excel 2010 Average Ignoring Zero Values - Non-consecutive cells

Hi,

I have cells that I need to work out averages for but I need the average to exclude any 0 values.
The cells are non-consecutive so I haven't been able to use averageif successfully.

As an example, I need to average f3,i3,l3,o3. Some of these are 0 and I want the average to exclude them.
I have about 900 rows of data and will be copying the formula down as the columns I need are all the same.

I would prefer a solution without an array formula if possible as other people are going to be populating this spreadsheet and they have minimal excel knowledge.

Thank you.

L

Hi,

Can I ask what's in e.g. cells G3, H3, J3, K3, etc. which is preventing you from averaging across a contiguous range? I presume they are numerical, since, if not, =AVERAGEIF(F3:O3,"<>0") would happily exclude them from its calculation.

I also notice that there is a pattern to the values which you wish to be averaged (every third cell) - is this always the case? Could you give a more definitive list of those cells which you wish to average (assuming it's more than just F3, I3, L3 and O3)?

Regards

Try this...

=AVERAGE(IF(F3<>0,F3,{""}),IF(I3<>0,I3,{""}),IF(L3<>0,L3,{""}),IF(O3<>0,O3,{""}))

Or...

=SUMPRODUCT(INDEX((MOD(COLUMN(F3:O3),3)=0)*F3:O3,,)/SUMPRODUCT((MOD(COLUMN(F3:O3),3)=0)*(F3:O3<>0)))

Regards

I was going in this direction:

Array entered**:

=AVERAGE(IF(MOD(COLUMN(F3:O3),3)=0,IF(F3:O3<>0,F3:O3)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

But figured that's kind of "overkill" to calculate just 4 cells.

"I was going in this direction:"

I was trying to avoid an array formula!

Edit: in any case, it'd be a shame to take the emphasis away from that {""}-syntax solution.

Regards

Here's another way.....

=SUM(F3,I3,L3,O3)/INDEX(FREQUENCY((F3,I3,L3,O3),0),2)

We don't know if there might be negative numbers. That's why I used <>0 instead of >0.

Good option, though.

Do you have any headers or similar in row 1 or 2 which would indicate which columns should be included? If so then you can use AVERAGEIFS and include the header criteria, e.g. if F1, I1, L1 and O1 all contain the text value "Total" you can use this formula

=AVERAGEIFS(F3:O3,F3:O3,"<>0",F$1:O$1,"Total")

You're welcome. We appreciate the feedback!