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

Hi,

Could you take an example.

Answer 3

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

This formula would appear to SUM() the even ordinal columns regardless of whether they are greater than zero or not and divide that total by a COUNT() of the even ordinal columns that are greater then zero. This might lead to unreliable results.

Answer 4

Hi,

Try this

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

Hope this helps.

Answer 5

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.

Hi,

If you get that error then it could be the numbers are really text that look like numbers. Try this in an empty cell

=ISNUMBER(D6)

Drag right and for every cell (D6, F6 etc) the formula should return TRUE. If it returns FALSE then your numbers aren't real numbers.

How are these numbers derived? Manually entered? Formula?

If the numbers are text then the best thing to do is fix that but this alternative should work.

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

Once again ARRAY entered.