| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Difficult function | Excel Worksheet Functions | |||
| Difficult Average Calculation <HELP | Excel Worksheet Functions | |||
| Difficult IF Function (For me at least) help | Excel Worksheet Functions | |||
| Difficult function: help needed. | Excel Discussion (Misc queries) | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Linear Mode
