Difficult Average Function
 

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

Difficult Average Function
 

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)

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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

Difficult Average Function
 

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

Difficult Average Function
 

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

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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

Difficult Average Function
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
Thank you! The array formula works. Thank you so very much. I really
appreciate your help! Connie

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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

Difficult Average Function
 

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

Difficult Average Function
 

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

Difficult Average Function
 

A bit shorter

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

Mike

"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

Difficult Average Function
 

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

Difficult Average Function
 

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

Difficult Average Function
 

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" wrote:

A bit shorter

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

Mike

"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

Difficult Average Function
 

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

"Connie Martin" wrote:

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

Difficult Average Function
 

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

Difficult Average Function
 

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

"Connie Martin" wrote:

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" wrote:

A bit shorter

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

Mike

"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

Difficult Average Function
 

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

Difficult Average Function
 

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

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
Thank you! The array formula works. Thank you so very much. I really
appreciate your help! Connie

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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

Difficult Average Function
 

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

Difficult Average Function
 

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

"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

Difficult Average Function
 

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

"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

Difficult Average Function
 

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

"Mike H" wrote:

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

"Connie Martin" wrote:

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

Difficult Average Function
 

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

"Connie Martin" wrote:

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

"Mike H" wrote:

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

"Connie Martin" wrote:

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

Difficult Average Function
 

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

"Mike H" wrote:

A bit shorter

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

Mike

"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

Difficult Average Function
 

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

"Mike H" wrote:

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

"Connie Martin" wrote:

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

"Mike H" wrote:

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

"Connie Martin" wrote:

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

Difficult Average Function
 

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

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
Thank you! The array formula works. Thank you so very much. I really
appreciate your help! Connie

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in
message
...
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