Cobbie,

It's an array and will do that unless you enter it by pressing
CTRL+Shift+Enter

Mike

"Connie Martin" wrote:

This one gives me #VALUE! The one T. Valko works except if there's no
reference to the date in A9 in A42:A5000, then I get #DIV/0! Connie

"Mike H" wrote:

Ragdyer,

I like that but prefer to add the check for ""

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

to truly emulate the behavour of =Average(....

Mike

"Ragdyer" wrote:

How about:

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

?
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mike H" wrote in message
...
A bit shorter


=IF(COUNTIF(A42:A5000,A9),AVERAGE(IF((A42:A5000)=A 9,IF(J42:J5000<"",J42:J50
00))),"")

Mike

"Mike H" wrote:

Maybe


=IF(ISERROR(AVERAGE(IF((A42:A5000)=A9,IF(J42:J5000 <"",J42:J5000)))),"",AVER
AGE(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