Calculate Average Based on Date Range

C

catguy

New Member
Joined
Jul 20, 2009
Messages
25
Hi,
I am having some trouble with a function to calculate the "average time to process" based on a specific date range. I think it may have to do with the date field being formatted as "mm/dd/yyyy h:mm:ss", but I have another formula that calculates processing time per "Lot", which is the reason for the date field format.

I would like to average the values in range "ProcTime" based on the date in range "Date1" (average "ProcTime" if "Date1" =12/16/2013)
I've tried AVERAGEIF both as a standard formula and an array formula, but am stuck.
Any help would be greatly appreciated.
Here is a snapshot of the data I am working with:
LotTypeDate1Date2ProcTime
010000535671010112/16/2013 2:57:57 PM12/17/2013 10:05:58 AM19:08:01
010000535642010112/16/2013 12:10:25 PM12/17/2013 8:33:13 AM20:22:48
010000535637010212/16/2013 11:53:01 AM12/17/2013 5:45:27 AM17:52:26
010000535636010212/16/2013 11:53:01 AM12/16/2013 4:05:26 PM4:12:25
010000535635010212/16/2013 11:53:00 AM12/17/2013 5:43:59 AM17:50:59
0100005356180112/16/2013 6:58:07 AM1/2/2014 4:00:46 PM321:02:39
0100005356160112/16/2013 6:57:33 AM12/17/2013 3:39:57 PM32:42:24
010000535672010112/16/2013 2:59:49 PM12/17/2013 9:21:50 AM18:22:01
0100005356140112/16/2013 6:56:48 AM12/17/2013 3:41:43 PM32:44:55
0100005356210112/16/2013 7:03:24 AM12/17/2013 10:11:19 AM27:07:55
0100005356120112/16/2013 6:56:10 AM1/9/2014 1:52:11 PM438:56:01
010000535496010212/16/2013 8:20:32 AM12/16/2013 9:23:02 AM1:02:30
0100005354930112/16/2013 8:01:37 AM12/16/2013 11:20:56 AM3:19:19
0100005354910112/16/2013 8:01:36 AM12/16/2013 10:06:07 AM2:04:31
0100005354880112/16/2013 8:01:34 AM12/16/2013 9:55:07 AM1:53:33
0100005361540112/16/2013 10:37:16 AM12/17/2013 8:57:54 AM22:20:38
0100005361530112/16/2013 10:37:15 AM12/17/2013 9:11:43 AM22:34:28
0100005356150112/16/2013 6:57:32 AM1/10/2014 3:20:56 PM464:23:24
010000535695010112/16/2013 3:19:36 PM12/18/2013 7:29:01 AM40:09:25
0100005356170112/16/2013 6:58:06 AM1/9/2014 1:51:04 PM438:52:58
010000535673010112/16/2013 3:00:13 PM12/17/2013 9:28:59 AM18:28:46
0100005356970112/16/2013 3:20:38 PM12/17/2013 10:03:38 AM18:43:00
0100005360330112/16/2013 8:57:43 AM12/16/2013 11:08:33 AM2:10:50
0100005360900112/16/2013 1:14:12 PM12/17/2013 10:24:32 AM21:10:20
0100005356920112/16/2013 3:15:33 PM12/18/2013 7:53:28 AM40:37:55
0100005356890112/16/2013 3:11:39 PM12/18/2013 8:00:34 AM40:48:55
010000535682010112/16/2013 3:06:44 PM12/18/2013 7:21:07 AM40:14:23
010000536116010112/16/2013 9:51:53 AM12/16/2013 1:03:12 PM3:11:19
010000535679010212/16/2013 3:05:52 PM12/17/2013 9:49:57 AM18:44:05
0100005356750112/16/2013 3:02:05 PM12/17/2013 9:16:33 AM18:14:28
010000535676010212/16/2013 3:02:07 PM12/17/2013 9:45:22 AM18:43:15
0100005372310112/17/2013 3:47:37 PM12/19/2013 8:37:20 AM40:49:43
010000537245010212/17/2013 4:02:09 PM12/19/2013 9:04:26 AM41:02:17
010000537244010212/17/2013 4:02:09 PM12/19/2013 9:13:09 AM41:11:00
010000537243010212/17/2013 4:02:09 PM12/19/2013 8:48:16 AM40:46:07
010000537228010212/17/2013 3:42:27 PM12/18/2013 9:02:02 AM17:19:35

<tbody>
</tbody><colgroup><col><col><col span="2"><col><col></colgroup>

<tbody>
</tbody>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
I've tried AVERAGEIF both as a standard formula and an array formula, but am stuck.
Click to expand...
Can you post your formula attempts?
They may just need a little tweaking.
 
Upvote 0
Thanks for the reply.

I found this formula worked once I placed an "*" after the date.... =AVERAGEIF(Date1,"=12/16/2013*",ProcTime), so I thnk I can tweak it from there to average the time by week/month.
I'm open to suggestions though.
 
Upvote 0
Maybe something like this:
Code: 
=AVERAGEIFS(ProcTime,Date1,">=" & DATEVALUE("12/16/2013"),Date1,"<" & DATEVALUE("12/17/2013"))
 
Upvote 0
Joe4 said:
Maybe something like this:
Code: 
=AVERAGEIFS(ProcTime,Date1,">=" & DATEVALUE("12/16/2013"),Date1,"<" & DATEVALUE("12/17/2013"))
Click to expand...
This didn't seem to work either, it just returned "#DIV/0!".
I entered this to get a monthly average, but the result is slightly off : =AVERAGEIFS(ProcTime,Date1,">=1/1/2015*",Date1,"<=1/31/2015*")
If I average by manually selecting the cells for January, the result is "20:01:44", but the result for the formula returns "23:46:51" (field is formatted as [h]:mm:ss)
 
Upvote 0
This didn't seem to work either, it just returned "#DIV/0!".
Click to expand...
That means that it is not finding any records within that date range.
Did you type the formally I provided, or Copy and Paste? If you typed, check for typos.
 
Upvote 0
Did you type the formally I provided, or Copy and Paste?
Click to expand...
I used copy and paste.
I usually work in Access, but the individual who will be responsible for maintining this data does not have access and needs to create the reports from this spreadsheet.
I can do everything I need to in access 2010, but the formulas are bit different and don't translate well to excel 2010
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Average in excel
Replies
3
Views
64
etaf
etaf
J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
98
etaf
etaf
C
  • Question
Incorrect date
2 3
Replies
25
Views
498
awoohaw
awoohaw
R
  • Solved
Import Data Date Format Wrong
Replies
9
Views
304
Georgiboy
Georgiboy
Z
  • Solved
Formula to find 'Part of the Day' from the Time Column
Replies
8
Views
303
Zee996
Z

Forum statistics

Threads
1,214,951
Messages
6,122,446
Members
449,083
Latest member
Ava19

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