Difficult Average Function

[Connie Martin]

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

 

[T. Valko]

Try one of these:

=SUMIF(A42:A5000,A9,J42:J5000)/COUNTIF(A42:A5000,A9)

Or, this array formula** :

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Mike H]

Hi,

This will include zeroes in the number range but ignore blanks

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

Array entered with CTRL+Shift+Enter


Mike

 

[Connie Martin]

Thank you! The array formula works. Thank you so very much. I really
appreciate your help! Connie

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Connie Martin]

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]

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

 

[Mike H]

A bit shorter

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

Mike

 

[Connie Martin]

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

 

[Ragdyer]

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

 

[Connie Martin]

This one leaves the cell blank that doesn't have a corresponding date, which
is what I want, but it puts #VALUE! in the cells where there were averages
and where there should be averages. Connie

 

[Mike H]

Connie,

Did you array enter it? It's identical to the previous formula except that
it first checks that the date exists in the range. If there are no instances
of the date it does nothing. If it finds an occurence of the date It then
averages all the corresponding numbers and excludes blanks which is a true
emulation of the ordinary average formula.

Mike

 

[Mike H]

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

 

[Mike H]

Remember ------ It's an ARRAY and must be commited with CTRL+Shift+Enter

 

[Connie Martin]

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

 

[Connie Martin]

Please see further posts here. I'm looking for something a little more in
your formula. So far, yours is the only one that works except when the A9
date is not found in A42:A5000, then I get #DIV/0! How do I fix that? Connie

 

[Mike H]

Cobbie,

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

Mike

 

[Connie Martin]

This one works except for where the date in A9 doesn't exist in A42:A5000.

 

[Connie Martin]

Sorry, Ragdyer. Yours works except where the date in A9 doesn't exist in
A42:A5000, then I get #DIV/0!

 

[Connie Martin]

Mike, my apologies!! The formula below works!! I edited it, because I
placed it in a cell searching for the date in A28 and forgot to press
Ctrl+Shift+Enter again. Please forgive me, and thank you very much!! Connie