averageif in range and on day of week returning #DIV/0

CONORF

New Member
Joined
Dec 30, 2014
Messages
20
Office Version
  1. 365
Hello, i am hoping someone will be able to offer assistance. I am trying to get the average of time during a range and on a specific day. it works fine separately with the range and with the day but when I combine the two i get a #div/0 error. Appreciate any assistance that can be offered.
 

Attachments

  • time calc.PNG
    time calc.PNG
    32.5 KB · Views: 12

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Does this in J2 do what you want?
Excel Formula:
=AVERAGEIFS(D:D,B:B,">="&F2,B:B,"<="&G2,A:A,J$1)
 
Upvote 0
Solution
Hello, i am hoping someone will be able to offer assistance. I am trying to get the average of time during a range and on a specific day. it works fine separately with the range and with the day but when I combine the two i get a #div/0 error. Appreciate any assistance that can be off

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Does this in J2 do what you want?
Excel Formula:
=AVERAGEIFS(D:D,B:B,">="&F2,B:B,"<="&G2,A:A,J$1)
Good morning. Thank you so much for your quick response. Unfortunately that is exactly the formula I already tried and results in the #div/0 error. I am not sure what is causing it. I'm wondering if it is formatting or data integrity that is causing it.
 
Upvote 0
Unfortunately that is exactly the formula I already tried and results in the #div/0 error. I am not sure what is causing it. I'm wondering if it is formatting or data integrity that is causing it.
It must relate to your data in some way as it worked fine with my test data. Perhaps you could try with some smaller test data and if still a problem post that small sample data with XL2BB so the we can copy to test with the exact same data.

BTW, please don't forget to update your version details as requested above. :)
 
Upvote 0
It must relate to your data in some way as it worked fine with my test data. Perhaps you could try with some smaller test data and if still a problem post that small sample data with XL2BB so the we can copy to test with the exact same data.

BTW, please don't forget to update your version details as requested above. :)
Thank you. I did add in my version.
time calc.xlsx
ABCDEFGHIJKLMNO
1DAYOFTHEWEEKREQUESTSTARTTIME DIFFERENCE/ REQUEST TO STARTRangeFridayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
2Friday10:16:00 AM2:38:00 PM4:227:00 AM6:59 PM#DIV/0!#DIV/0!
3Friday2:07:00 PM5:17:00 PM3:107:00 PM10:59 PM     
4Friday9:05:00 PM 11:00 PM6:59 AM     
5Friday7:43:00 AM10:17:00 AM2:34     
6Friday3:01:00 AM      
7Friday12:54:00 AM2:46:00 AM1:52     
8Friday2:17:00 AM3:54:00 AM1:37     
9Friday7:09:00 PM8:48:00 PM1:39     
10Friday5:25:00 AM      
11Friday7:47:00 PM9:03:00 PM1:16     
12Friday8:16:00 AM9:35:00 AM1:19     
13Friday11:39:00 AM12:58:00 PM1:19     
14Friday3:24:00 AM5:09:00 AM1:45     
15Friday8:18:00 PM9:32:00 PM1:14     
16Friday3:05:00 AM4:35:00 AM1:30     
17Friday9:29:00 PM10:52:00 PM1:23     
18Friday7:33:00 PM8:49:00 PM1:16     
19Friday6:18:00 PM7:35:00 PM1:17     
20Friday8:20:00 AM9:27:00 AM1:07     
21Friday7:59:00 AM9:09:00 AM1:10     
Sheet1
Cell Formulas
RangeFormula
H2H2=AVERAGEIFS(D:D,B:B,">="&F2,B:B,"<="&G2,A:A,J1)
I2I2=AVERAGEIF(A:A,I1,D:D)
J3:N21J3=IF(AND($B3>$F3,$B3<$G3,$A3=J2),$D3,"")
D2:D21D2=IF(C2="","",C2-B2)
 
Upvote 0
Thank you. I did add in my version.
time calc.xlsx
ABCDEFGHIJKLMNO
1DAYOFTHEWEEKREQUESTSTARTTIME DIFFERENCE/ REQUEST TO STARTRangeFridayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
2Friday10:16:00 AM2:38:00 PM4:227:00 AM6:59 PM#DIV/0!#DIV/0!
3Friday2:07:00 PM5:17:00 PM3:107:00 PM10:59 PM     
4Friday9:05:00 PM 11:00 PM6:59 AM     
5Friday7:43:00 AM10:17:00 AM2:34     
6Friday3:01:00 AM      
7Friday12:54:00 AM2:46:00 AM1:52     
8Friday2:17:00 AM3:54:00 AM1:37     
9Friday7:09:00 PM8:48:00 PM1:39     
10Friday5:25:00 AM      
11Friday7:47:00 PM9:03:00 PM1:16     
12Friday8:16:00 AM9:35:00 AM1:19     
13Friday11:39:00 AM12:58:00 PM1:19     
14Friday3:24:00 AM5:09:00 AM1:45     
15Friday8:18:00 PM9:32:00 PM1:14     
16Friday3:05:00 AM4:35:00 AM1:30     
17Friday9:29:00 PM10:52:00 PM1:23     
18Friday7:33:00 PM8:49:00 PM1:16     
19Friday6:18:00 PM7:35:00 PM1:17     
20Friday8:20:00 AM9:27:00 AM1:07     
21Friday7:59:00 AM9:09:00 AM1:10     
Sheet1
Cell Formulas
RangeFormula
H2H2=AVERAGEIFS(D:D,B:B,">="&F2,B:B,"<="&G2,A:A,J1)
I2I2=AVERAGEIF(A:A,I1,D:D)
J3:N21J3=IF(AND($B3>$F3,$B3<$G3,$A3=J2),$D3,"")
D2:D21D2=IF(C2="","",C2-B2)
I also added in the mini sheet. I appreciate any help you can offer.
 
Upvote 0
It must relate to your data in some way as it worked fine with my test data. Perhaps you could try with some smaller test data and if still a problem post that small sample data with XL2BB so the we can copy to test with the exact same data.

BTW, please don't forget to update your version details as requested above. :)
thank you . I was able to eventually get it to work by correcting my formatting in my data. The only problem I am having now is getting the formula to work on the after midnight calculations. I created a separate post since that is a different question. Thank you again.
 
Upvote 0
Hi

I added two columns.
Mappe5
EF
1HourShift
210A
Tabelle1
Cell Formulas
RangeFormula
E2E2=HOUR([@REQUEST])
F2F2=IF(AND([@Hour]>=7,[@Hour]<19),"A","B")


And inserted a pivot table.
Mappe5
AB
1Average of TIME DIFFERENCE/ REQUEST TO STARTDAYOFTHEWEEK
2ShiftFriday
3A02:02
4B01:30
Tabelle2
 
Upvote 0
I did add in my version.

I also added in the mini sheet.
Thanks for both of the above. (y)


I was able to eventually get it to work by correcting my formatting in my data. The only problem I am having now is getting the formula to work on the after midnight calculations. I created a separate post since that is a different question.
I assume that you worked out that the problem was that column B, although showing just time, actually included date and time. I didn't see a separate post/thread so I have made a suggestion here.
See if the following works for you, even though column B still actually included a date as well as a time. I would strongly advise not to use whole column references in these formulas as that could negatively impact the sheet performance. Where I have used row 1000, use something that is sure to be big enough to cover any amount of data that you might have.
Note that I have slightly altered the sample data and the added colour was simply to show where those two coloured results in J2 and H4 came from.

23 05 08.xlsm
ABDEFGHIJ
1DAYOFTHEWEEKREQUESTTIME DIFFERENCE/ REQUEST TO STARTRangeFridayMondayTuesday
2Friday10:16:00 AM4:227:00 AM6:59 PM2:09#N/A1:10
3Friday2:07:00 PM3:107:00 PM10:59 PM1:21#N/A#N/A
4Friday9:05:00 PM 11:00 PM6:59 AM1:41#N/A#N/A
5Friday7:43:00 AM2:34
6Friday3:01:00 AM 
7Friday12:54:00 AM1:52
8Friday2:17:00 AM1:37
9Friday7:09:00 PM1:39
10Friday5:25:00 AM 
11Friday7:47:00 PM1:16
12Friday8:16:00 AM1:19
13Friday11:39:00 AM1:19
14Friday3:24:00 AM1:45
15Friday8:18:00 PM1:14
16Friday3:05:00 AM1:30
17Friday9:29:00 PM1:23
18Friday7:33:00 PM1:16
19Friday6:18:00 PM1:17
20Friday8:20:00 AM1:07
21Tuesday7:59:00 AM1:10
Average Times
Cell Formulas
RangeFormula
H2:J3H2=AVERAGE(FILTER($D$2:$D$1000,(MOD($B$2:$B$1000,1)>=$F2)*(MOD($B$2:$B$1000,1)<=$G2)*($A$2:$A$1000=H$1),NA()))
H4:J4H4=AVERAGE(FILTER($D$2:$D$1000,((MOD($B$2:$B$1000,1)>=$F4)+(MOD($B$2:$B$1000,1)<=$G4))*($A$2:$A$1000=H$1),NA()))
D2:D21D2=IF(C2="","",C2-B2)
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top