Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Friday, June 10, 2016 8:10 PM
I have a worksheet with date/times in column a and values in column b. I want to get an average of the b values where the hour is between 8 and 18. I have tried several variations on AverageIF and AverageIFS but always get a #DIV/0 error. Any suggestions greatly appreciated!
=AVERAGEIF(A:A,"AND(HOUR(A:A)>=8,HOUR(A:A)<=20)", P:P)
| 6/10/2016 14:30 | 1,950 |
| 6/10/2016 14:29 | 2,089 |
| 6/10/2016 14:28 | 2,075 |
| 6/10/2016 14:26 | 2,045 |
| 6/10/2016 14:25 | 2,162 |
| 6/10/2016 14:24 | 1,988 |
| 6/10/2016 14:22 | 2,120 |
| 6/10/2016 14:21 | 1,971 |
| 6/10/2016 14:20 | 2,146 |
| 6/10/2016 14:18 | 1,889 |
| 6/10/2016 14:17 | 2,012 |
| 6/10/2016 14:16 | 2,022 |
| 6/10/2016 14:15 | 1,879 |
| 6/10/2016 14:13 | 1,958 |
| 6/10/2016 14:12 | 1,811 |
| 6/10/2016 14:11 | 1,984 |
| 6/10/2016 14:09 | 1,911 |
| 6/10/2016 14:08 | 1,987 |
| 6/10/2016 14:07 | 1,854 |
| 6/10/2016 14:06 | 1,929 |
| 6/10/2016 14:04 | 1,907 |
| 6/10/2016 14:03 | 1,638 |
| 6/10/2016 14:02 | 1,266 |
| 6/10/2016 14:00 | 1,485 |
| 6/10/2016 13:59 | 1,832 |
| 6/10/2016 13:58 | 1,913 |
All replies (3)
Saturday, June 11, 2016 12:20 AM âś…Answered
Your attempted formula doesn't correspond to your description (column P vs column B, and 20 vs 18).
Here are two ways to do it. Avoid using whole columns.
1) Without a helper column: use the following array formula, confirmed with Ctrl+Shift+Enter:
=AVERAGE(IF((HOUR(A1:A26)>=8)*(HOUR(A1:A26)<=18),P1:P26))
2_ With a helper column: assuming that the dates/times begin in A1, enter the following formula in C1:
=HOUR(A1)
and fill down to the end of the data. Use the following 'ordinary' formula (not an array formula) for the average:
=AVERAGEIFS(P1:P26,C1:C26,">=8",C1:C26,"<=18")
For both methods, adjust the ranges and the boundary hours as needed.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
Monday, June 13, 2016 5:01 AM
Hi Bill Hunt,
Here I think that your issue is more related with Excel Application.
so for better response and better solution to your issue I move this thread to Excel it pro discussions forum.
The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.
Regards
Deepak
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.
Monday, June 13, 2016 7:40 AM
Hi Bill Hunt,
Please refer to the reply of Hans Vogelaar, if you think the reply of Hans is helpful, I suggest you mark it as answer. Other partners who read the forums with the same issue can get more information from the correct result.
If you need more help, please let me know and I'm glad to help you.
Regards,
Emi Zhang
TechNet Community Support
Please mark the reply as an answer if you find it is helpful.
If you have feedback for TechNet Support, contact [email protected].