AVERAGE and COUNTIF errors

Question

Hi,

I am trying to use a formula to average a selection of cells within a row. No matter how I try it I keep getting an error that I can't figure out or see why it doesn't work.

I want to average cells D6, F6, H6, J6, L6, N6, P6, R6, T6, V6 but only averaging cells greater than 0.

I have tried to use the average formula divided by this =COUNTIF((D6,F6,H6,J6,L6,N6,P6,R6,T6,V6),">0") but it doesn't like it!!! In my head it makes sense.

Any help greatly appreciated!

I also tried this to no avail =AVERAGEIF((D6,F6,H6,J6,L6,N6,P6,R6,T6,V6),">0")

Answer 1

... Could you take an example.

An AVERAGE() is defined as the SUM() divided by the COUNT() which you've tried to provide. The OP wanted the first, then every second column in the D6:V6 range considered for a total of 10 columns. Of the 10 columns, a further condition of greater then zero was added. These two conditions must be applied to both the SUM() and the COUNT(). You have only applied the first one to the SUM() but both to the COUNT().

     

Your proposed formula is in A6. My own is in A7 and Mike H's array formula solution is in A8. By not considering the greater than zero condition in the SUM() portion of your formula, you've skewed the results. Of course, this will only occur if there are values less than zero, so I used the term unreliable.

You just need to add the (D6:V6>0) condition to your first SUMPRODUCT(). Example:

=SUMPRODUCT((MOD(COLUMN(D6:V6),2)=0)*(D6:V6>0)*(D6:V6))/SUMPRODUCT((MOD(COLUMN(D6:V6),2)=0)*(D6:V6>0))

This pretty much is the way I proposed my own solution above with the exception that you've used MOD(...,2)=0 instead of ISEVEN(...) to determine column criteria.

Answer 2

WADR, you might want to try those first two formulas again There is no functional difference between that last formula that I offered and the first one that I replied with and the array formula that was offered is absolutely correct.

MOD() returns the remainder of a division operation. If you are looking for even numbers, then MOD(<number>, 2) should equal 0. With odd numbers, it will equal 1.

MOD function
ISEVEN function
ISODD function
COLUMN function

Answer 3

... Could you take an example.

An AVERAGE() is defined as the SUM() divided by the COUNT() which you've tried to provide. The OP wanted the first, then every second column in the D6:V6 range considered for a total of 10 columns. Of the 10 columns, a further condition of greater then zero was added. These two conditions must be applied to both the SUM() and the COUNT(). You have only applied the first one to the SUM() but both to the COUNT().

     

Your proposed formula is in A6. My own is in A7 and Mike H's array formula solution is in A8. By not considering the greater than zero condition in the SUM() portion of your formula, you've skewed the results. Of course, this will only occur if there are values less than zero, so I used the term unreliable.

You just need to add the (D6:V6>0) condition to your first SUMPRODUCT(). Example:

=SUMPRODUCT((MOD(COLUMN(D6:V6),2)=0)*(D6:V6>0)*(D6:V6))/SUMPRODUCT((MOD(COLUMN(D6:V6),2)=0)*(D6:V6>0))

This pretty much is the way I proposed my own solution above with the exception that you've used MOD(...,2)=0 instead of ISEVEN(...) to determine column criteria.

This last formula here has worked a treat. Many thanks all. I established that you changed the =0 to an =1 for the odd columns right?

Not sure why the other formula's kicked up errors. I double checked all the cells were number cells too.

So to be secure in my knowledge of the forumla the MOD(COLUMN(D6:V6),2)=0 selects even columns and =1 is odds? What doe the ,2 bit do in that formula?