We help IT Professionals succeed at work.

I need an updated excel formula

wrt1mea
wrt1mea asked
on
I need an updated excel formula to only average the values in a range that actual have data in them. I am currently using:

=IF(A2=A1,"",AVERAGEIF($A:$A,A2,$K:$K))

That formula is also factoring in blank cells, and I only need it to average cells that have values (Dates) in them, i.e., >0

See the attached example. I am starting with Column O Cell 1. I will need to update P1: P10 also, but I think I should be able to handle that based on someones solution for Column O.
12-5-11-Rev.xlsx
Comment
Watch Question

Awarded 2010
Top Expert 2013

Commented:
Do a sumif divided by a countif

Commented:
It currently shows 187 and this seems to be the expected result from what I have understood of the query. what was your expected result in column o?
Awarded 2010
Top Expert 2013

Commented:
This perhaps?
=SUMIF(A:A,">0",K:K)/COUNTIF(A:A,">0")
Awarded 2010
Top Expert 2013

Commented:
Although the same works with
=AVERAGEIF(A:A,">0",K:K)

Author

Commented:
I only need it to compute the average IF there is a date in Column H.

I forgot to include that in the original question. Sorry.
Awarded 2010
Top Expert 2013
Commented:
I think you want this.

=AVERAGEIFS(K:K,H:H,">0",A:A,A2)

Computes the average of all that match in A and have dates in H.
Awarded 2010
Top Expert 2013

Commented:
The xxxIFS formulas are the same except you put the range to calculate on first and then can supply as many range/criteria pairs as you want.
Commented:
=IF(OR(A2=A1,H2=""),"",AVERAGEIF($A:$A,A2,$K:$K))

Author

Commented:
Works Great!