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

C

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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
CONORF said:
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
Click to expand...

Peter_SSs said:
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)
Click to expand...
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
CONORF said:
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.
Click to expand...
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
Peter_SSs said:
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. :)
Click to expand...
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
CONORF said:
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)
Click to expand...
I also added in the mini sheet. I appreciate any help you can offer.
 
Upvote 0
Peter_SSs said:
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. :)
Click to expand...
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
CONORF said:
I did add in my version.
Click to expand...

CONORF said:
I also added in the mini sheet.
Click to expand...
Thanks for both of the above. (y)


CONORF said:
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.
Click to expand...
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
You must log in or register to reply here.

Similar threads

P
  • Solved
Counting number of cells with formula returning a value OR a #DIV/0 error?
Replies
3
Views
229
Eric W
Eric W
J
  • Solved
blank cells & #DIV/0! error
Replies
4
Views
112
Joe4
Joe4
W
  • Question Question
Getting rid of #DIV/0! in Average function
Replies
5
Views
730
awoohaw
awoohaw
J
  • Solved
Averages with multiple conditions
Replies
2
Views
244
Fluff
Fluff
M
  • Question Question
Custom Time Format in Pivot Table #DIV/0!
Replies
2
Views
443
MaxxLevell
M

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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