Start Free Trial

asked on

I need an updated excel formula

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

Do a sumif divided by a countif

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?

This perhaps?
=SUMIF(A:A,">0",K:K)/COUNTIF(A:A,">0")

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

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Works Great!