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

You can exclude the odd numbered columns in the D6:V6 range by coupling ISEVEN(COLUMN(...)) together in a SUMPRODUCT() formula.

=SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0))                   ◄ count greater than zero

=SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0)*(D6:V6))       ◄ sum greater than zero

Put these together for a psuedo-AVERAGEIF(),

=SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0)*(D6:V6))/SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0))

Note that I have not compensated for a #DIV/0! error in the case that there are no values greater than zero. An IFERROR() wrapper could catch that eventuality like this,

=IFERROR(SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0)*(D6:V6))/SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0)),0)

Hi, using the IFERROR version at the end, I get a 0.00 value rather than the correct average. I'm assuming its getting an error and then putting in the 0.

Using the SUMPRODUCT one I get the divide by zero error.

Answer 3

Excel gives me a divide by zero error for this one!?

I have two values greater than zero in the array and the rest are 0.

Answer 4

Hi,

You could use this ARRAY formula, see below on how to enter it

=AVERAGE(IF(((MOD(COLUMN(D6:V6),2)=0)*(D6:V6>0)),D6:V6))

This is an array formula which must be entered by pressing CTRL+Shift+Enter

and not just Enter. If you do it correctly then Excel will put curly brackets

around the formula {}. You can't type these yourself. If you edit the formula

you must enter it again with CTRL+Shift+Enter.

Answer 5

You can exclude the odd numbered columns in the D6:V6 range by coupling ISEVEN(COLUMN(...)) together in a SUMPRODUCT() formula.

=SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0))                   ◄ count greater than zero

=SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0)*(D6:V6))       ◄ sum greater than zero

Put these together for a psuedo-AVERAGEIF(),

=SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0)*(D6:V6))/SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0))

Note that I have not compensated for a #DIV/0! error in the case that there are no values greater than zero. An IFERROR() wrapper could catch that eventuality like this,

=IFERROR(SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0)*(D6:V6))/SUMPRODUCT(ISEVEN(COLUMN(D:V))*(D6:V6>0)),0)