Difficult Average Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Connie Martin - 05 Oct 2008 19:18 GMT

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

Reply to this Message

T. Valko - 05 Oct 2008 19:32 GMT

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)

Signature

Biff
Microsoft Excel MVP

>I am stumped. How in the world do I write this formula?
>
[quoted text clipped - 3 lines]
> advance.
> Connie

Reply to this Message

Connie Martin - 05 Oct 2008 19:48 GMT

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

> Try one of these:
>
[quoted text clipped - 14 lines]
> > advance.
> > Connie

Reply to this Message

T. Valko - 05 Oct 2008 20:21 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Thank you! The array formula works. Thank you so very much. I really
> appreciate your help! Connie
[quoted text clipped - 19 lines]
>> > advance.
>> > Connie

Reply to this Message

Connie Martin - 05 Oct 2008 21:10 GMT

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

> You're welcome. Thanks for the feedback!
>
[quoted text clipped - 21 lines]
> >> > advance.
> >> > Connie

Reply to this Message

T. Valko - 05 Oct 2008 21:46 GMT

Having read all the other replies I guess you got something that works?

Signature

Biff
Microsoft Excel MVP

> 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
[quoted text clipped - 26 lines]
>> >> > advance.
>> >> > Connie

Reply to this Message

Mike H - 05 Oct 2008 19:41 GMT

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

> 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

Reply to this Message

Connie Martin - 05 Oct 2008 20:25 GMT

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

> Hi,
>
[quoted text clipped - 12 lines]
> > advance.
> > Connie

Reply to this Message

Mike H - 05 Oct 2008 20:32 GMT

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

> 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!
[quoted text clipped - 18 lines]
> > > advance.
> > > Connie

Reply to this Message

Mike H - 05 Oct 2008 20:39 GMT

A bit shorter

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

Mike

> Maybe
>
[quoted text clipped - 24 lines]
> > > > advance.
> > > > Connie

Reply to this Message

Ragdyer - 05 Oct 2008 20:47 GMT

How about:

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

?

Signature

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 !
---------------------------------------------------------------------------

> A bit shorter

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

> Mike
>
> > 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
> >
[quoted text clipped - 20 lines]
> > > > > advance.
> > > > > Connie

Reply to this Message

Connie Martin - 05 Oct 2008 21:13 GMT

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

> How about:
>
[quoted text clipped - 41 lines]
> > > > > > advance.
> > > > > > Connie

Reply to this Message

Connie Martin - 05 Oct 2008 20:50 GMT

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

> A bit shorter
>
[quoted text clipped - 30 lines]
> > > > > advance.
> > > > > Connie

Reply to this Message

Mike H - 05 Oct 2008 20:59 GMT

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

> 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
[quoted text clipped - 34 lines]
> > > > > > advance.
> > > > > > Connie

Reply to this Message

Connie Martin - 05 Oct 2008 21:22 GMT

Mike, the shorter version works, too. Again, after I entered it I revised it
to A28 instead of A9 and forgot to press Ctrl+Shift+Enter again! My
apologies. Thank you. Connie

> A bit shorter
>
[quoted text clipped - 30 lines]
> > > > > advance.
> > > > > Connie

Reply to this Message

Connie Martin - 05 Oct 2008 20:47 GMT

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

> Maybe
>
[quoted text clipped - 24 lines]
> > > > advance.
> > > > Connie

Reply to this Message

Mike H - 05 Oct 2008 20:52 GMT

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

> 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
[quoted text clipped - 30 lines]
> > > > > advance.
> > > > > Connie

Reply to this Message

Connie Martin - 05 Oct 2008 21:15 GMT

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

> Connie,
>
[quoted text clipped - 40 lines]
> > > > > > advance.
> > > > > > Connie

Reply to this Message

Mike H - 05 Oct 2008 21:20 GMT

Your welcome. I'm now going to lie down in a darkened room!!

> 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
[quoted text clipped - 44 lines]
> > > > > > > advance.
> > > > > > > Connie

Reply to this Message

Connie Martin - 05 Oct 2008 21:38 GMT

Mike, you're hilarious!! I burst out laughing when I read your post!!
Thanks for your humour and sorry I got so muddled up!! I think I'm the one
that should go lie down in a darkened room! Connie

> Your welcome. I'm now going to lie down in a darkened room!!
>
[quoted text clipped - 46 lines]
> > > > > > > > advance.
> > > > > > > > Connie

Reply to this Message