Calculate average based on specific time or date

I have data of travel time between 2 locations each day. There are
columns such as date, start time and end time in multiple columns.

How can I find the average of travel time on Monday, Tuesday, Wed etc
and the average of travel time if start time is between 7:00 to 7:30,
etc..?

Thanks for the help,


0
9/30/2008 12:17:54 PM
excel 39879 articles. 2 followers. Follow

3 Replies
820 Views

Similar Articles

[PageSpeed] 34

cpliu,

I have come up with a suggestion.  Basically, I took your "average" two 
ways.  One way would be to look at the Total Average for all days over time, 
and the second would be to look at the individual days themselves.

Basically, the start and stop time can be Differenced out (C2 - B2), with 
that you can get an average travel time by taking the =AVERAGE(D2:D11) 
formula.  The second alternative (by Day) would require you to write a 
SUMIF/COUNTIF statement.  My example has the formula 
=SUMIF(A2:A11,"Mon",D2:D11)/COUNTIF(A2:A11,"Mon") as per below.

	Start	Stop	Diff		
Mon	7:00	8:00	1:00		Total Avg
Tue	7:15	8:45	1:30		1:07
Wed	7:02	7:58	0:56		
Thu	7:00	8:00	1:00		Mon Avg
Fri	7:15	8:45	1:30		0:58
Mon	7:02	7:58	0:56		
Tue	7:00	8:00	1:00		
Wed	7:15	8:45	1:30		
Thu	7:02	7:58	0:56		
Fri	7:00	8:00	1:00		

I hope this answers your question.

-- 
--Thomas [PBD]
Working hard to make working easy.


"cpliu" wrote:

> I have data of travel time between 2 locations each day. There are
> columns such as date, start time and end time in multiple columns.
> 
> How can I find the average of travel time on Monday, Tuesday, Wed etc
> and the average of travel time if start time is between 7:00 to 7:30,
> etc..?
> 
> Thanks for the help,
> 
> 
> 
0
ThomasPBD (13)
9/30/2008 1:17:00 PM
> Basically, the start and stop time can be Differenced out (C2 - B2), with
> that you can get an average travel time by taking the =3DAVERAGE(D2:D11)
> formula. =A0The second alternative (by Day) would require you to write a
> SUMIF/COUNTIF statement. =A0My example has the formula
> =3DSUMIF(A2:A11,"Mon",D2:D11)/COUNTIF(A2:A11,"Mon") as per below.

Thank you, Thomas. That's great. I have all the date entered as month,
day, year like 08/21/2008. Is there a function that I can convert them
to Monday, Tuesday, etc in a different column?

Thanks,

cpliu
0
9/30/2008 2:12:43 PM
Hi

In column E
=TEXT(A1,"ddd")
Base your formulae on column I and D rather than A and D
=SUMIF(E2:E11,"Mon",D2:D11)/COUNTIF(E2:E11,"Mon")

Alternatively, if you didn't want to use another column
=SUMPRODUCT(--(Weekday(A2:A11,2)=1),D2:D11))/SUMPRODUCT(--(Weekday(A2:A11,2)=1))
where 1 = Monday, 2 = Tuesday etc.
-- 
Regards
Roger Govier

"cpliu" <spamfreeliu@yahoo.com> wrote in message 
news:65762f14-77fd-443f-9081-7c9b798f1bf1@k30g2000hse.googlegroups.com...
>
>> Basically, the start and stop time can be Differenced out (C2 - B2), with
>> that you can get an average travel time by taking the =AVERAGE(D2:D11)
>> formula.  The second alternative (by Day) would require you to write a
>> SUMIF/COUNTIF statement.  My example has the formula
>> =SUMIF(A2:A11,"Mon",D2:D11)/COUNTIF(A2:A11,"Mon") as per below.
>
> Thank you, Thomas. That's great. I have all the date entered as month,
> day, year like 08/21/2008. Is there a function that I can convert them
> to Monday, Tuesday, etc in a different column?
>
> Thanks,
>
> cpliu 

0
Roger
9/30/2008 2:31:24 PM
Reply:

Similar Artilces:

Payment date issues
Is there a way to avoid the irritation of Money 2005 refusing to make an electronic payment because it thinks there is not enough time to make the payment deadline specified in a recurring payment? Having to edit the occurrence is a real pain in the xxxx. Pls reply to mail address. Thanks. wizard_chef@hotmail.com Money gets this information from the financial institution making the payment -- so perhaps they can help you with the deadlines. -- <stokely@uab.edu> wrote in message news:1120064173.782289.14860@g14g2000cwa.googlegroups.com... > Is there a way to avoid the irritati...

change x axis date scale on multiple charts quarterly
Have multiple charts to update weekly during a calendar quarter, need to change the x axis date scale on every chart quarterly. Can the x axis date range be changed on multiple charts simultaneously ? If your x axis range comes from a series on your spreadsheet, changing the values in the spreadsheet will change all the charts which use that series to define the x axis. -- David Biddulph Paul wrote: > Have multiple charts to update weekly during a calendar quarter, need > to change the x axis date scale on every chart quarterly. > Can the x axis date range be changed on multiple...

UTC time to standard time
I have an sql database that I am importing data from, into excel. One of the fields has a 32 bit integer that represents the number of seconds elapsed from 1/1/70 to current time based on UTC. I would like to convert this to a standard excel date and time. Anyone know an easy way to do this. I know I could write a function that would use divison and modela functions to arrive at the answer, but I thought there might be an easier way. Any suggestions appreciated. Dennis You could use this formula =integer/86400+"01/01/1970" can be written as =integer/86400+25569 ...

Nita Time formula
This was a reply to nita. Are all the cells formatted for time? I have them all formatted for time (13:30) and I get " #VALUE! " in the cell with the formula. Doug Keep your responses in the original thread, otherwise people don't know what you are talking about. Regards, Fred "Doug AllPool" <dougallpool@aol.com> wrote in message news:uN3PCRwtKHA.3408@TK2MSFTNGP06.phx.gbl... > This was a reply to nita. > > Are all the cells formatted for time? I have them all formatted for time > (13:30) and > I get " #VALUE...

query timing out. how do you stop this from happening?
What can be done to keep a querie from constantly timing out? Almost all my queries and functions are doing this lately as my database grows. Any advice on this is very much welcome. Thanks, MC -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200705/1 ...

How set -send on behalf- for multiple mailboxes at the same time.
See question. "Prem" <premmetje@zonnet.nl> wrote: >See question. See answer? -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm Don't send mail to this address mailto:h.pott@getronics.com Or to these, either: mailto:h.pott@pinkroccade.com mailto:melvin.mcphucknuckle@getronics.com mailto:melvin.mcphucknuckle@pinkroccade.com Sorry I mean see title: How set "send on behalf" for multiple mailboxes at the same time. It seems one can only perform this setting per mailbox? Windows 2003 , Exchange Server 2003 R...

Requery / Refresh calculated texbox
Dear friends, On a form I have two textboxes (cbo1 and cbo2). I need to have a formula in cbo2 (eg [cbo1]*10, that will use what I type in cbo1. The problem is that I need by changing the value in cbo1, immediately to have the result in cbo2. e.g. I type 1 in cob1 = 10 will be shown in cbo2, by typing more in cob1, e.g. 11, in cbo2 will be shown 110. This should be done before update? Where shall I use the requery method? Thanking you in advance, GeorgeC You can do this one of two ways: Either Private Sub cbo1_AfterUpdate() Me.cbo2 = Me.cbo1 * 10 End Sub or set the Control Sou...

Fill date series in table
I have an attendance sheet that 1 day per week. I want to extend a series across the top row. (I.E. Jan 1, Jan 8, Jan 15, Jan 22, etc.). The sheets are formatted as tables. When I grab the handle to copy the cells it will only copy, not fill the series. How can I get a weekly series? In A1 enter: 1/1/2010 In B1 enter: =A1+7 and copy across -- Gary''s Student - gsnu200909 If you put Jan 1 in the first cell, Jan 8 in cell next to it and then select **both** of these cells... you can now copy this selection across the row and the series will fill in automatically for...

Running Sum used in calculation in Report
I have 12 columns, two are running sums (work great), I need those two running sum calculations to multiply together and that result in another column. I have tried a number of solutions and I'm still stuck. I hope I'm giving enough information. In the below example 2, 81, 4 and 84 are results of running sums of other columns. 2 81 here I need the result of 2*81 4 84 here I need the result of 4*84 I would expect that if the totals were needed in the same report section, all you would need to do is reference the names of the text boxes in an expression like: ...

Developer Extensions And Run-time Access 2007
Am I understainding correctly that the developer extentions are available as a free download as well as the run-time version of Access 2007? On Dec 14, 8:59 am, Huckle <Huc...@discussions.microsoft.com> wrote: > Am I understainding correctly that the developer extentions are available as > a free download as well as the run-time version of Access 2007? That's true. I'm using them now. Keith That's correct. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Huckle" <Huckle@discussions.microsoft.com> wrote...

Convert Base 36 to base 10
I have been given a spreadsheet with transaction numbers converted into base 36- alpha numeric - I need it in base 10- number format- I have approx 30,000 of these!- is ther a formula to convert from 1 to another? -- Thanks for your help Are you sure your numbers are Base 36? I ask because I kind of suspect your "digits" are these... 0, 1, 2, ...., 9, A, B, ..., X, Y, Z and if that is the case, then you actually have Base 37 numbers and not Base 36. For Base36 numbers, the letter Z would not be in your set of digits... Z would be the 37th digit because 0 is the f...

Struggling with TIME calculations ...
Hey all... Here's what I am trying to do: A3 = 4:00am B3 = A3 - 1 I keeping getting #value error in B3 instead of the result I am hoping to get which is 3:00am. I have also tried the following in B3: = A3 - TIME(1,0,0) ---> Again the #value error ... Even tried = A3 - SUM(01:00) Can anyone offer a suggestion. Thanks, Greg See "Subtracting Times": http://www.cpearson.com/excel/datearith.htm ************ Hope it helps! Anne Troy www.OfficeArticles.com "Greggy" <greg.kelway@gmail.com> wrote in message news:1138908606.083299.13300@g14g2000cwa.goo...

Need to change date in field to whatever I want
I have a database for my research. I need to change the date, so that I do not have any restraints on my admissions. I have checked the data field for date of admission and it looks exactly the same in the data field, but for some reason, the date will not allow me to move forward with 2010. I am not sure what to do. Can you please help. This seems to be the only field that does not change to what I need it to change to. I currently am able to use the date for 2010. in other date fields except for one field. Please any suggestions. I am very new in setting or using access. ...

Time Input Mask to show "5:35pm" ONLY
Hi there, I am trying to get me date/time field in both the table and form to show only 0:00pm... 5:35pm as an example. I have tried a few different input masks the closest one I have got is 0:00\pm, however when I type the time into the field (by only typing 535) it automatically changes to 5:35AM. I don't need the AM option at all as all times entered will be a PM time. Therefore I want it to automatically update as PM. I also only want to have to type the numbers as indicated before 535. I don't want the leading zero (05:35pm) and I dont want to have to type the : no...

Time frame for 2010 Outlook Beta
Can someone tell me how long the Beta option for Office 2010 Outlook will be available? Will it extend past the issuance of Office 2010 in June? In other words, how long will I be able to use it before having to purchase the new office.? Gene L.;1256435 Wrote: > Can someone tell me how long the Beta option for Office 2010 Outlook > will be > available? Will it extend past the issuance of Office 2010 in June? In > other > words, how long will I be able to use it before having to purchase the > new > office.? I don't know the exact date, but i kno...

Average time from date time column?
I have a column that is in general date format, 6/19/2007 5:34:23 PM. In another column I have a value between 10 and around 50. What I have to do is get the average of this number by time in a day. So if the number is 10 all day, then the average is 10. If the number is changed during the day, say its 5 for half the day then 10 for the other half of the day, then the number I'm looking for is 7.5. If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day then the number would be around 8.5..... if you follow what I'm trying to say. So far I have been a...

Rolling Averages?
I am trying to graphically determine an estimated projected date in the future that an account will reach a certain balance, and need a formula to resolve this. Because the amounts being credited are random in date and amount, I need to simplify things by assuming that the next amount credited will be a mean average of all credits to the account spread over the time since the first payment. Since the data is organised one row per day, each day should show the average of all payments made so far since day one, and the same each day in the future, so we can create a graph of pro...

Manual Calculation in Excel 2007 with Calculate mode set to Automatic
Hello, I'm desperate to understand why some of my s/s are in manual calculation mode even though the options are set to Automatic calculation. I have checked under Excel's options and the formula bar to confirm calculation mode and cannot make the s/s change to automatic. Any advice? Julie try saving the workbook after you make the change "Julie White" wrote: > Hello, > > I'm desperate to understand why some of my s/s are in manual > calculation mode even though the options are set to Automatic > calculation. I have checked under Exce...

calculating clock times
if i enter "7:00 am in one cell and 5:45 pm in another cell, what formula do in enter in another cell to caluclate the time? On Sun, 7 Mar 2010 11:04:01 -0800, gma <gma@mircosoft.com> wrote: >if i enter "7:00 am in one cell and 5:45 pm in another cell, what formula do >in enter in another cell to caluclate the time? If the first cell is A1 and the other cell is A2, try the following formula in a third cell: =A2-A1 Format the third cell to display the time difference the way you like. Hope this helps / Lars-�ke hi do you mean subtract or add?? ...

Word: The document 'Filename' caused a serious error the last time ...
Upon opening some previously created Word 2003 documents, users on our network are receiving the message: The document 'Filename' caused a serious error the last time it was opened. Would you like to continue opening it? At which point the document can not be opened and the user is presented with the option to pull available unformatted text from the <ocument. ... or ... Word stops responding completely and you have to end-task on Word. Searching Microsoft will tell that you can remove the documents in question from the disabled list in Word. Indeed you can, but thi...

time based calendar editing
am wondering how i can get the functionality to do time based calendar editing with outlook and exchange. We want to be able to create a calendar which is only edited by one person and has a small handfull of people set as reviewer. The person set as editor can only edit during certain times of day but cannot edit during other times of the day. We tried blocking off a section of time, as an appointment, in outlook and marked it as busy. That will stop items sent as meeting requests but won't stop the editor from opening up the resource calendar directly and placing another appo...

SLA monitoring
I want to be able to monitor SLA's. I am taking the time the call is logged and need to check whether is has been responded to within 4 hours. I have added a workflow that adds 4 hours to the case created time and put the result in a new field. What I need it to do is ignore time between 17:30 and 08:00 and also weekends - so a call logged at say 16:00 on a Tuesday will have a respond by of 11:00 Wednesday - any ideas anyone please?? ...

Annual return calculation hosed vs. me being stupid
I need to know which. I've been trying to transfer an IRA account from an old financial institution that doesn't have online services to a new one that does. If I follow explicitly the instructions MS gives to do this, I lose the transaction history and cost basis of the underlying investments. It simply sets up a new account. If I set up a transfer instead, I keep the cost basis and it's accurate, but I find I'm now earning an average annual return of around 3000% on the underlying mutual funds... the small cap fund is booming along nicely at 52000%. Needless to say I...

Dialog is lossing focus all the time!!
Hi, I have a dialog box that ask the user to confirm a window operation. The user must confirm within a certain amount of time otherwise the operation is automatically cancelled. It all works fine, the dialog is set as topmost and the 'cancel operation' button is set as default. But if something else happens, for example a new email arrives, (outlook pops-up with a "You have a new Email"), or if you were busy opening another window. Then my dialog looses it's focus. It remains topmost but it no longer has focus. How can i ensure that my dialog, NEVER looses focus, som...

How do I average a column without including zeros
I want to average a column that is all percentages, but I want to excluding any cells with a 0%, what formula would I use? Thanks!!! Maybe.. =SUM(A1:A10)/COUNTIF(A1:A10,"<>"&0) KMHarpe wrote: > > I want to average a column that is all percentages, but I want to excluding > any cells with a 0%, what formula would I use? Thanks!!! -- Dave Peterson There may be a snappy built-in function in Excel to do this, but until someone responds with it, this formula will work: =SUM(D1:D5)/COUNTIF(D1:D5,"<>0") .... where D1:D5 is the range containin...