This wipes out cells that did have averages in them, leaving them blank.
Could we leave out the part about skipping cells with blanks because that
makes no difference in the average. I average three 5's and the answer was
5. I then averaged three 5's with two additional blank cells. The answer
was still 5. Connie

"Mike H" wrote:

Maybe

=IF(ISERROR(AVERAGE(IF((A42:A5000)=A9,IF(J42:J5000 <"",J42:J5000)))),"",AVERAGE(IF((A42:A5000)=A9,IF (J42:J5000<"",J42:J5000))))

Once again Array entered with CTRL+Shift+Enter

"Connie Martin" wrote:

Thank you, Mike. I have a further question to both of you.....In A42:A5000
there might not be the date in A9. In that case, I get the error #DIV/0!
How do I fix that?

Connie

"Mike H" wrote:

Hi,

This will include zeroes in the number range but ignore blanks

=AVERAGE(IF((A42:A5000)=A9,IF(J42:J5000<"",J42:J5 000)))

Array entered with CTRL+Shift+Enter


Mike

"Connie Martin" wrote:

I am stumped. How in the world do I write this formula?

In a cell, I want it to find the date in A9 in A42:A5000 and then average
the numbers in J42:J5000 for that date. Hope this is clear. Many thanks in
advance.
Connie