average if with time

I have data sorted by date(mm/dd/yy) and time(hh.mm.ss) and I am tryin
to write a function which will average every hour. How can I sor
through the times and compute an average without getting all the 2 
clocks, for example, as opposed to the hour on one particular day
Thanks for the help,

Andrew Moo

--
Message posted from http://www.ExcelForum.com

0
5/24/2004 5:48:20 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
651 Views

Similar Articles

[PageSpeed] 21

Need a little more detail.  Maybe give me an example of what you have a
data and then tell me what results you are trying to recieve from tha
data!

michae

--
Message posted from http://www.ExcelForum.com

0
5/24/2004 5:51:53 PM
I see your problem as having separate date and time cells. There's no need
to have separate cells -- you can have both a date and a time in a cell. I
would put the two fields together (=a1+b1), then average this new cell.

-- 
Regards,
Fred
Please reply to newsgroup, not e-mail


"anjem >" <<anjem.16ruii@excelforum-nospam.com> wrote in message
news:anjem.16ruii@excelforum-nospam.com...
> I have data sorted by date(mm/dd/yy) and time(hh.mm.ss) and I am trying
> to write a function which will average every hour. How can I sort
> through the times and compute an average without getting all the 2 o
> clocks, for example, as opposed to the hour on one particular day.
> Thanks for the help,
>
> Andrew Moon
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
fredsmith99 (155)
5/24/2004 6:00:35 PM
The data is all in integer format, so averaging wouldn't be a problem
It is data collected for every minute in the hour and what I want to d
is sum and average every hour of data. For example:

1	5/4/2002	6:36:00	14010	280
2	5/4/2002	6:37:00	230	11234
3	5/4/2002	6:38:00	10922	626

I want to average to find iterations per minute and total iteration
per hour. This is only three minutes of data, I want to do it for a
hour. Thank

--
Message posted from http://www.ExcelForum.com

0
5/24/2004 6:01:12 PM
If the times are in a separate column use

=AVERAGE(IF(A2:A20=DATE(2004,4,1),B2:B20))

where B2:B20 holds the times and A2:A20 the dates and the date you want to
average is 04/01/04

if they are in the same column

=AVERAGE(IF(INT(A2:A20)=DATE(2004,4,1),A2:A20))

both formulas entered with ctrl + shift & enter

format result as time

-- 


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

"anjem >" <<anjem.16ruii@excelforum-nospam.com> wrote in message
news:anjem.16ruii@excelforum-nospam.com...
> I have data sorted by date(mm/dd/yy) and time(hh.mm.ss) and I am trying
> to write a function which will average every hour. How can I sort
> through the times and compute an average without getting all the 2 o
> clocks, for example, as opposed to the hour on one particular day.
> Thanks for the help,
>
> Andrew Moon
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre081 (3244)
5/24/2004 6:03:48 PM
If you have times listed as you showed, then at the end of each hour
have a formula in the cell to the right for total and average.
=SUM(C7:C66)
=ROUND(AVERAGE(C7:C66),0)

This should help!
Michae

--
Message posted from http://www.ExcelForum.com

0
5/24/2004 6:15:01 PM
Reply:

Similar Artilces:

How many times does a control trigger a "MODIFIEDCHANGED" event???
Ok, I'm pretty new to VB.Net, so please bear with me. Imagine the Load event of one of my forms includes the following: Dim ctrl as Control Dim txtbox As TextBox For Each ctrl In Me.Controls If TypeOf ctrl Is TextBox Then txtbox = CType(ctrl, TextBox) AddHandler txtbox.ModifiedChanged, AddressOf TextBoxChanged End If Next ....now here is what my "TextBoxChanged" routine looks like: Private Sub TextBoxChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) MessageBox.Show("Hello World") End Sub ....Now imagine my form has (amongst o...

Need help converting date time to date only
I have a column in my spreadsheet with date and time, you can see in m attachment. I want to convert this column in to date only. Pleas help. Thanks Geng Attachment filename: sales - open orders test.xls Download attachment: http://www.excelforum.com/attachment.php?postid=57210 -- Message posted from http://www.ExcelForum.com Hi without looking at your file you could use the following formula in a helper column =INT(A1) and format this cell as 'date' -- Regards Frank Kabel Frankfurt, Germany > I have a column in my spreadsheet with date and time, you can see in ...

messages downloaded multiple times
I am having a problem where my messages are getting dowloaded from the server multiple times. The problem is described at: http://support.microsoft.com/default.aspx?scid=kb;en- us;Q317945 I followed the instructions and upgraded to SP2 but it did not fix the problem. I have 20+ copies of all messages in my inbox. It only started doing this about a week ago and worked fine before that. ...

First time open Outlook get communication error yet website CRM wo
Every morning when I open Outlook, I get a "problem communicating with CRM" error. I try with IE and it works fine, so there is no problem with CRM. If I close Outlook, then open it again, it's fine. Why is it always failing the first time? ...

Nothing happens when a user change time on a serviceactivity
When one of our users is changing time on a serviceactivity nothing happens in the schedule. But when I look at the serviceactivity a little bit later I can see the change. When I create a serviceactivity the time is changing directly.. Does anyone know why? Is it the field "Show time as waiting" that is the case? ...

Averaging numbers but ignoring < and
I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.137) 0.3 <0.001 0.01 <0.01 0.1 Thank you On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote: >I have a column of data as illustrated below than contains numbers, blank >cells, dashes and < entries. Is it possible to average only ...

Time Function
Is there a function that allows you to automatically enter the time of day in a cell based other information being entered in another cell (IF..Then) and not have the time automatically updated every time you open the spreadsheet? Take a look here: http://www.mcgimpsey.com/excel/timestamp.html In article <7BB4F41E-202A-4676-B066-1FFEBD67E40D@microsoft.com>, "Donovan" <Donovan@discussions.microsoft.com> wrote: > Is there a function that allows you to automatically enter the time of day in > a cell based other information being entered in another cell (IF...

averaging less than values
How do I average a column of numbers where less than values are calculated as that number? 2 <2 3 4 <2 5 In the average I want the<2 to = 2. So the answer should be 3 -- JD Assuming source data as posted in A2:A7 Paste this in say, B2, then press CTRL+SHIFT+ENTER to array-enter the formula: =AVERAGE(SUBSTITUTE(A2:A7,"<","")+0) Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "JD" wrote: > How do I average ...

Calculating cost from time
Hi This is such a great forum for us novices!! Thanks to all. I have a timesheet that calculates the no of hours someone worked Which works fine but when I come to times it by the hourly rate i comes up wrong. (A) (B) Total 27:05 (hh:mm) Rate �9.45 Total �10.66 Any suggestions? Thank -- Iain ----------------------------------------------------------------------- IainG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=275 View this thread: http://www.excelforum.com/showthread.php?threadid=27188 Hi in B3 enter =A1*24*B1 and format as number ...

Removing time from a date field
There is a date column in my excel spreadsheet. The data in this column has both date and time. I want to strip off the time part using VBA. Please let me know if there is an elegant way to do this. advTHANKSance, Kirubakaran. www.kirubakaran.com I found the answer. I used the INT function. Change the format to date -- Don Guillett SalesAid Software donaldb@281.com "kulalosai" <kirubakaran@gmail.com> wrote in message news:1115658705.112166.113400@o13g2000cwo.googlegroups.com... > There is a date column in my excel spreadsheet. The data in this column > has both da...

Average of Averages
Another seemingly simple process which I can't figure. :) I have a list of averages, and I want to get the average of them. As such, A 1 25% 2 42% 3 100% 4 50% I need a formula to average something like the above. Thanks, Alan =AVERAGE(A1:A4) is one possible solution. Hope it helps. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=513937 Hi Alan It really depen...

Averages
Hello again, I have an interesting situation. I basically inherited an older database that is used to enter dates, usage and charges for accounts. The situation is that we are attempting to do averages filtered by account, and averaged against the number of days that data has been entered. So, for example, we had 7 months of data entered, our query should total the number of days in the seven months (x), then average that with the total usage number (y). This data currently resides in the same table labeled "data" in the form of from and to dates (6/1/1999 to 7/1/1999) and usage (32...

time and date difference
I have a cell format of dd-mm-yyyy hh:mm that I have applied to "call open date and time" and a "call closed data and time" columns. I'd like to have a further column that calculates the difference in days and hours, or just hours, between these two date/time values. I can't seem to find a way to do it tho. I have tried DATEDIF but it doesn't give me the results I want (formula =DATEDIF(A1,B1,"d")) and I have seen examples of calculating the difference between time values but again, not what I want exactly. Is there a way to do this or do I have t...

Converting time to decimal then rounding
Hi I have a formula =MOD(T11-R11,1)*24 Where T11 is 22:43 and R11 is 22:34 The difference of these time values is 9 minutes or .15. Currently I am getting the result of .1 because it is not rounding up. The result I need is ..2 -- so that Excel will round .01 to .04 down and .05 to .09 up. How should I adjust my formula? Thanks!! Maybe: ROUNDUP((A1-A2)*24,1) Micky "JB Bates" wrote: > Hi > > I have a formula > > =MOD(T11-R11,1)*24 > > Where T11 is 22:43 and R11 is 22:34 > > The difference of these time values i...

Excel should let me enter time in one hundreds of a second.
I am a volunteer Track & Field coach for 13 years, and would like to use Excel to keep my team's satistics. But am unable to enter time as minutes, seconds, tenth of seconds, or hundreth of seconds. Probably because I don't know how to do it without converting minutes to seconds. Sure would be nice to be able to enter exact times and query for totals and averages. Hopefully the newer version of Excel/Office will have this capability, and that I can afford it. I would rather use Office then have to purchase a seperate program called "Team Manager" ------------...

Average Lead Time
Does anybody know if the average lead time in Item Vendor Maintenance counts weekends, or only business days? The average lead time is calcualted as follows: Enter the average number of days that pass between the time you place an order with this vendor and the time you receive the order. If you're using Purchase Order Processing, this field will be updated each time a shipment or shipment/invoice is posted in Purchase Order Processing. The average lead time is calculated using the following formula: [(Number of Receipts) * (Average Lead Time) + (Received Date - Ordered Date)] / (...

Average Function
Hi group, I have a formula in a cell that takes the average Sales of the 12 rows (for Jan to Dec) like so: =Average(A1:A12)The problem is that for example, since therea are no sales figures from August to Dec, the average is underestimated because it averages it over the entire range of cells (though they are zero from Augus to Dec). How can I make this Average function to be smarter and only average those months that the sales is not zero (August to Dec is Not BLANK but its zero) Hi Average is a very simple thing: SUM/COUNT. So =SUM(A1:A12)/12 should do for all years with 12 mont...

Entering a time into excel
I am trying to enter a time in military time ie: 1500 for 3:00 PM. Everytime it puts 0:00. I have tried formatting the cell everyway possible. I don't need a date stamp just the time for time cards. I would like the end result to display standard time 3:00 PM. Any help would be greatly apreciated. Thanks in advance. Mike In order for Excel to recognize input as a time (as opposed to the number 1,500), you must enter a colon. You can enter 15:00, and it will be accepted as 3:00 pm (regardless of the formatting). You can then format the cell as hhmm, which will display...

Windows Installer runs every time I get a new email message
Hi: I am running XP and MS Office 2003. In Outlook, each time I get a new message, Windows Installer runs ("preparing to install"). So if I get 75 new messages, that message flashes 75 times. Any ideas on how to fix this? Thanks, Richard I am having the same problem and have not been able to find a solution. Did you have any luck yet? Thanks in advance for any help you can provide. Tim "Richard" wrote: > Hi: > > I am running XP and MS Office 2003. In Outlook, each time > I get a new message, Windows Installer runs ("preparing > to install&...

Excel Time Logged Out
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel As of recently (over the last few days), whenever I use Excel, I am unable to shut down my computer afterward without it freezing. A message appears saying something along the lines of &quot;excel log out failure&quot;, and the only way to end the program is to force quit. After I force quit, I'm able to shut down the computer. I was wondering what was causing this and how to fix it. <br><br>Thanks. No idea, and this one may take a while to find. You haven't stated your update ve...

averages
Hi All Trying to work out the best way to get an average answer based on a set of answers The answer will be 1 - 10 across a number of questions e.g B C D E F Answer Q1 Q2 Q3 Q4 1 1 1 3 3 2 1 0 1 2 3 0 1 3 3 4 1 0 1 1 5 1 1 6 7 6 2 2 5 2 7 2 3 3 4 8 2 1 7 6 9 1 2 1 3 10 2 2 8 7 Total 13 13 38 38 Average ? ? ? ? What is the best way to get the average response to each question. i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure this is right....

One Time Formula
I have a spreadsheet for my budget and I need to calculate how much is needed from my savings account so that my wife and I have $700.00 to live on each month. I calculate this by subtracting the balance in my checking account from the total of the bills to be paid that month. This needs to be a one time calculation as the amount of the bills to be paid each month can change but the amount needed from savings should remain the same. Is there a way to have this formula run just once at the beginning of each month? Thank you in advance for any help on this. -- JungleJim74 ...

Time-problem
Hello I have a timesheet where I enter start and end times. My problem is that I want to check if any or all of the elapsed time falls into a certain timeframe. Here is an example. If I put 07:00 into D4 and 16;00 into G4 in H4 the answer is 9, Which is correct (I use C Pearsons formula so I can calculate shifts over midnight.. Then I want to se if the timeframe specified falls into into a timeframe between 19:00 to 06:00 and show the result in I4. D4 G4 H4 I4 04:00 14:00 10 2 17:00 23:00 6 4 21:00 05:00 8 ...

FRx "run- time error '6' overflow"
I have had two user report intermittent "run- time error '6' overflow" when trying to open FRx. Later it works fine. We are on 6.7 sp10. I don't see anything here or in the knowledgebase specific to this error. Can someone give me an idea what to look at for the cause? Do you have a dual monitor? We have seen this on occasion when the screen resolution is set too low. "DavidM" wrote: > I have had two user report intermittent "run- time error '6' overflow" when > trying to open FRx. Later it works fine. We are on 6.7 sp...

Charting Run Times
Ok, here's the deal. I have a data table on sheet 1. It is organized like this: Column A: Start Time "A" Column B: End Time "A" Column C: Start Time "B" Column D: End Time "B" Row 1: 8/30/2005 8:30:00 PM Row 2: 8/30/2005 9:07:00 PM Row 3: 8/31/2005 1:21:00 AM Row 4: 8/31/2005 3:31:00 AM What I am trying to do is chart this data as a stacked bar going from left to right so that the first section of the bar (furthest to the left) represents the time between column "A" and column "B", and the second/right section ...