Difficult Average Function

  • Thread starter Thread starter Connie Martin
  • Start date Start date
C

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
 
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)
 
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
 
Thank you! The array formula works. Thank you so very much. I really
appreciate your help! Connie
 
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
 
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
 
A bit shorter

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

Mike
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
Cobbie,

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

Mike
 
Sorry, Ragdyer. Yours works except where the date in A9 doesn't exist in
A42:A5000, then I get #DIV/0!
 
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
 
Back
Top