How do I sample hourly data one time per day

I have hourly data from an instrument and would like to reduce the data set 
(see example below) to one point per day, either by selecting a specific time 
(e.g., 00:00) each day, or by averaging all of the values for one day.  The 
data files may contain more than one month's worth of data per file (i.e., 
quite large files).


8/11/2006	00:00	17.06
8/11/2006	01:00	17.05
8/11/2006	02:00	17.05
8/11/2006	03:00	17.05
8/11/2006	04:00	17.05
8/11/2006	05:00	17.05
8/11/2006	06:00	17.06
8/11/2006	07:00	17.06
8/11/2006	08:00	17.05
8/11/2006	09:00	17.06
8/11/2006	10:00	17.06
8/11/2006	11:00	17.06
8/11/2006	12:00	17.06
8/11/2006	13:00	17.06
8/11/2006	14:00	17.06
8/11/2006	15:00	17.06
8/11/2006	16:00	17.06
8/11/2006	17:00	17.06
8/11/2006	18:00	17.06
8/11/2006	19:00	17.05
8/11/2006	20:00	17.06
8/11/2006	21:00	17.06
8/11/2006	22:00	17.06
8/11/2006	23:00	17.06
8/12/2006	00:00	17.06
8/12/2006	01:00	17.05
8/12/2006	02:00	17.06
8/12/2006	03:00	17.06
8/12/2006	04:00	17.06
8/12/2006	05:00	17.06
8/12/2006	06:00	17.06
8/12/2006	07:00	17.06
8/12/2006	08:00	17.06


0
Merkin (2)
9/11/2006 3:53:02 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
330 Views

Similar Articles

[PageSpeed] 8

Get the average with

=AVERAGE(IF(A1:A100=--"2006-08-11",B1:B1000)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Once you have a full set of averages, you can select all the data, then
Edit>Pastespecial>Values, and delete the original data.

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Doc Merkin" <Doc Merkin@discussions.microsoft.com> wrote in message
news:B2B93812-5A24-4FA9-B7CF-E9F5FB35DA1A@microsoft.com...
> I have hourly data from an instrument and would like to reduce the data
set
> (see example below) to one point per day, either by selecting a specific
time
> (e.g., 00:00) each day, or by averaging all of the values for one day.
The
> data files may contain more than one month's worth of data per file (i.e.,
> quite large files).
>
>
> 8/11/2006 00:00 17.06
> 8/11/2006 01:00 17.05
> 8/11/2006 02:00 17.05
> 8/11/2006 03:00 17.05
> 8/11/2006 04:00 17.05
> 8/11/2006 05:00 17.05
> 8/11/2006 06:00 17.06
> 8/11/2006 07:00 17.06
> 8/11/2006 08:00 17.05
> 8/11/2006 09:00 17.06
> 8/11/2006 10:00 17.06
> 8/11/2006 11:00 17.06
> 8/11/2006 12:00 17.06
> 8/11/2006 13:00 17.06
> 8/11/2006 14:00 17.06
> 8/11/2006 15:00 17.06
> 8/11/2006 16:00 17.06
> 8/11/2006 17:00 17.06
> 8/11/2006 18:00 17.06
> 8/11/2006 19:00 17.05
> 8/11/2006 20:00 17.06
> 8/11/2006 21:00 17.06
> 8/11/2006 22:00 17.06
> 8/11/2006 23:00 17.06
> 8/12/2006 00:00 17.06
> 8/12/2006 01:00 17.05
> 8/12/2006 02:00 17.06
> 8/12/2006 03:00 17.06
> 8/12/2006 04:00 17.06
> 8/12/2006 05:00 17.06
> 8/12/2006 06:00 17.06
> 8/12/2006 07:00 17.06
> 8/12/2006 08:00 17.06
>
>


0
bob.NGs1 (1661)
9/11/2006 5:57:36 PM
> ... or by averaging all of the values for one day. 

One way to easily do the above ..

Assuming source data in cols A to C, from row1 down, 
where col C = values

Put in D1:
=AVERAGE(OFFSET(INDIRECT("C"&ROW(A1)*24-24+1),,,24))
Copy down as far as required 
(copy down by as many days as the data covers)

D1 returns the average of C1:C24 (day1)
D2 returns the average of C25:C48 (day2), and so on

This part of the expression: ROW(A1)*24-24+1
is left intentionally unsimplified to show the pattern 
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Doc Merkin" wrote:
> I have hourly data from an instrument and would like to reduce the data set 
> (see example below) to one point per day, either by selecting a specific time 
> (e.g., 00:00) each day, or by averaging all of the values for one day.  The 
> data files may contain more than one month's worth of data per file (i.e., 
> quite large files).
> 
> 
> 8/11/2006	00:00	17.06
> 8/11/2006	01:00	17.05
> 8/11/2006	02:00	17.05
> 8/11/2006	03:00	17.05
> 8/11/2006	04:00	17.05
> 8/11/2006	05:00	17.05
> 8/11/2006	06:00	17.06
> 8/11/2006	07:00	17.06
> 8/11/2006	08:00	17.05
> 8/11/2006	09:00	17.06
> 8/11/2006	10:00	17.06
> 8/11/2006	11:00	17.06
> 8/11/2006	12:00	17.06
> 8/11/2006	13:00	17.06
> 8/11/2006	14:00	17.06
> 8/11/2006	15:00	17.06
> 8/11/2006	16:00	17.06
> 8/11/2006	17:00	17.06
> 8/11/2006	18:00	17.06
> 8/11/2006	19:00	17.05
> 8/11/2006	20:00	17.06
> 8/11/2006	21:00	17.06
> 8/11/2006	22:00	17.06
> 8/11/2006	23:00	17.06
> 8/12/2006	00:00	17.06
> 8/12/2006	01:00	17.05
> 8/12/2006	02:00	17.06
> 8/12/2006	03:00	17.06
> 8/12/2006	04:00	17.06
> 8/12/2006	05:00	17.06
> 8/12/2006	06:00	17.06
> 8/12/2006	07:00	17.06
> 8/12/2006	08:00	17.06
> 
> 
0
demechanik (4694)
9/12/2006 1:21:02 AM
Reply:

Similar Artilces:

Become Debt Free In 30 Days
Become Debt Free In 30 Days TURN A $10 INCOME INTO THOUSANDS MONTHLY. Impossible? Oprah Winfrey, 20/20 and the Wall Street Journal put it through a test to prove it wouldn't work. But the FundingActivity did work and the experts admitted they were wrong! Thousands of people are quietly building small fortunes with this simple $10 strategy. The question is... Do you have the "guts" to prove it will work for you? You don't have to win a lottery to be financially free! A few minutes of your time is all it takes after clicking on this link: http://www.tiptopwebsite.com/...

Macro
Hi all!!! I am trying to get my macro to run a sort in a non specific cell for column D.....there are many steps up to this point, I will include the last couple in the code...here is what I have so far.... Columns("C:C").Find(What:="x").EntireRow.Insert Columns("C:C").Find(What:="x").EntireRow.Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Sort Key1:=Range("D63"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientati...

How do I copy data from one workbook to another whilst retaining formulas on original
I'm looking to run a macro from workbook SWT (sheet CLT) which will cu and paste data from a range of cells (A2,C2,E2,F2,G2,I2,K2.. A3,C3,E3,F3,G3,I3,K3... A4,C4,E4,F4,G4,I4,K4.... until end of data from workbook SWT (Sheet CS) to rows A to G in Workbook SWTS (Shee Stats). I have vlookup formulas (eg =IF(B2="","",VLOOKUP($B2,'Data Field'!$A$7:$B$12,2))) in place in columns A,C,E,F,G,I,K on sheet CS and I'm looking to keep the these formulas in place (unless someone ca think of better alternatives). When I paste the data on sheet Stats i must paste onto the...

Data Analysis
Hi, In Excel 2000 I used a feature called Data Analysis. Now I have Excel 2003 and I cant find this feature anywhere?? Any help appreciated. thanks, Paul Load the Analysis Toolpak Add-in (Tools/Add-ins...). In article <070401c4d7f5$3f64da70$a401280a@phx.gbl>, "Paul" <anonymous@discussions.microsoft.com> wrote: > Hi, > > In Excel 2000 I used a feature called Data Analysis. Now I > have Excel 2003 and I cant find this feature anywhere?? > > Any help appreciated. > > thanks, > > Paul ...

Data fill question
Hi all, How do I do an auto fill on a certain amount of cells in a range of numbers but at the end of the number there is a dash s. For example: 343-s, 344-s,345-s and so on. I tried to do the auto fill but it is just repeating the sequence of numbers? You could use a formula. With the first cell filled in (like A1), put this in A2 and fill down: =(LEFT(A1,FIND("-",A1)-1)+1)&"-s" HTH Jason Atlanta, GA >-----Original Message----- >Hi all, >How do I do an auto fill on a certain amount of cells in a range of numbers >but at the end of the number the...

Calculating transactions by time
Here's my problem, I have customer transaction data that shows deposits and withdrawals for a month. My data includes the customer ID, type of trans, date of trans, time of trans and amount. I am trying to come up with a way to count the number of times a customer makes a deposit then immediately makes a withdrawal. Obviously this will occur on the same date and within a minute or less of the deposit. I can pull a query showing the two relevent transactions together in a table but how do I count the number of occurrences. Any help is appreciated. Thanks! SELECT COUNT(...

Put data rows per sheet
I have an Excel workbook with 2 sheets. One of them is for data entering (e.g. rows: employee's name, ID#, working dates, fee etc.) and the other presents that data among other constant value cells (e.g. name of company, titles of columns, place for signatures etc.). What I want is when data in the presentation sheet reach at 22 rows to be automatically continued to the next page of the sheet. So, each printed page will always shows 22 rows of data with the constant data ABOVE and BELOW of these 22 rows. Thanks, Hi not fully automatic but try the following two macros (assumption: row 1 ...

graph data from multiple worksheets
I have about 10 worksheets with data in them, I would like to put it all on one chart, how do I do it? I cannot fit it all in one worksheet. Produce the chart from the data from your first workshet. Go to your second worksheet, select the relevant data, copy, go to your chart, edit/ paste special, and accept the relevant option to add a new data series or add data to an existing series. Alternatively having produced your original chart, go to Source Data, and either adding data to an existing series or adding a new series will allow you to select the relevant data from whichever work...

Is it possible to have text and currancy in one cell and ...
Is it possible to have text and currancy in one cell and be able to use the numbers in that cell to make an equation with another? The short answer .. No -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "thegen" <thegen@discussions.microsoft.com> wrote in message news:1552FF10-9CAA-42AD-B401-B0D229D6A343@microsoft.com... > Is it possible to have text and currancy in one cell and be able to use the > numbers in that cell to make an equation with another? well maybe depending on how the "text & currency" was ar...

Segrigation of different datas from one colum
Hi , I am trying to segrigate and pull data from one colum to different colums. For this I want to created a macro . Also I want this data to be pulled from a word document and to be pasted on an Excel template.Is it possible ??then How can we do it . Hi Roy- Providing a sample of the type of data you are working with (whatever you want to segregate, also called "parsing") would be helpful in order to provide you more direction. You can definitely extract data from a Word document, but how efficient it is will vary depending on how your Word document was set up...

convert hours into minutes
I searched and found a question just like mine but the answer did not work for me. here is waht I have: A1 21:05 A2 22:10 A3 =A2-A1 result 1:05 I want to convert the 1 hout and 5 minutes to 65 minutes. Thank you. Change the format of the cell with the formula in it to [m]. You can do this by going to Format | Cells | Number. Choose Custom and type [m] in the line provided -- tj "Tony" wrote: > I searched and found a question just like mine but the answer did not work > for me. > here is waht I have: A1 21:05 A2 22:10 A3 =A2-A1 result 1:05 I want to > c...

5000 character limit in data fields
Is there any way to extend the 5000 character limit for data fields? I have one field that will be at least 10000 characters regularly. It appears that I cannot make a custom field larger then 5000 characters though even with the ntext format. Just wanted you to know that we have just created a 5000 character ntext field in the Leads entity and get an error going offline with that data. Microsoft is working on a fix for this, but in the meantime we're stuck. Can you use the Notes Entity? "fbell@itstrategists.com" wrote: > Is there any way to extend the 5000 character...

network time
if one of my domain controllers in a site has the windows time service stopped will the clients/server authenticating in that site still look to the pdc for time? if i run the w32tm /resync command on a machine when the service is running is syncs successfully. If the service is stopped i get: C:\ w32tm /resync Sending resync command to local computer... The computer did not resync because no time data was available. PDC is in another site in the domain. Is this normal? I cant seem to find any documentation on this exact question. figured as much. only one dc in the ...

Scrolls Far Below Actual Data
I have a spreadsheet in which the moving piece in the vertical scroll bar is very narrow and, if you move it to the bottom, you have gone far below the bottom of the data. I tried deleting all rows (i.e. entire rows) between the end of my data and the scroll end, but this didn't seem to have any effect. Then I tried deleting rows all the way to the bottom of the spreadsheet. This didn't help either. Any ideas on why this is happening or how to fix it? Thanks in advance, Will Hello roadkill- Try this... Select all rows below your data, go to Edit>Clear>All, then close...

Split combined date time data #2
From file dump have combined date time cells eg "14/04/03 14:20" (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) See one answer in reply to your slightly later post. In article <EDD7065A-E5AA-40AB-BF38-50707E04109B@microsoft.com>, "Mark Ada" <MarkAda@discussions.microsoft.co...

Outlook 2003 event with no specific time that occurs on the 1st and 15th of every month?
What is the best way to make an event with no specific time that occurs on the 1st and 15th of every month? Make it an all day event? Recurring on the 1st and 15th - 2 separate events works better. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, Kevin Zawicki asked: | What is the best way to make an event with no specific time that | occurs on the 1st and 15th of every month? ...

Changing the data label position below the chart axis.
I'm charting some percentages. Many of them are negatives, but not all. The Y axis in my chart is more in the middle, instead of on the bottom, as with normal charts that have all positive numbers. The data labels are overlapping some of bars in the chart that are negatives. I can't get the data labels to move any further south. I've already changed the 'data label distance from axis' to the maximum of 1000. The data labels are still overlapping. Is there anything else I can try? The chart looks terrible with the labels partially on the negative bars, please help! H...

can you add a day counter for a colum and row
trying to add a day counter so I can see how long an item has been in stock. It would need to be able to use 1 coloum and each row in that colum. Hi Depends how you have your data set up, but assuming date into stock is column A =IF(A1="","",TODAY()-A1) Format>Cell>Number>General Copy down as required -- Regards Roger Govier "poohgld" <poohgld@discussions.microsoft.com> wrote in message news:31454DD6-A5C5-40E3-A0E8-9F3827E73551@microsoft.com... > trying to add a day counter so I can see how long an item has been in > stock. > It wo...

Replace data by VALUE or COMMENTS not availabel in Excel 2003 ???
I upgraded to Office 2003 and noticed that the option to find and replace data by VALUE or COMMENTs is not available to me anymore. I can only look by FORMULAS. Is it something that I have left out when doing the Advanced Options Install or is it no longer available? Click Options to further define your search, and then do any of the following: In the Within box, select Sheet or Workbook to search a worksheet or an entire workbook. The steps used to be.... 1. In the Search box, click By Rows or By Columns. 2. In the Look in box, click Formulas, Values, or Comments. Now number 2 only sho...

Separate file with one field
Seems like fairly simple need but cant figure it out. How do i export the contents of One field of a table into a .csv or a text file, one record per line? Thanks Ramesh Create a query that returns only one field. Use TransferText (in code or a macro) to export the query. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ramesh" <ramesh2020@gmaildotcom> wrote in message news:e6cgFNDKIHA.4880@TK2MSFTNGP03.phx.gbl... > Seems like fairly simple ...

Importing Excel mail merge data doc to outlook?
Does anyone know if this can be done for a start and if so, what needs to be done. I have tried the import/export - from excel wizard in outlook but have so far only managed to import a lot of entrys that end up as blank contacts. This leads me to believe that it can be done if the settings of the excel document are correct - i just need to figure out what the correct settings are??!!! ...

Percent of time
Hi I would like to know how to get percent of time? i.e. 30% of 12:00 or 70 of 11:00 and still able to sum up / add-up the total results automatically? Thanks! If the source times are real times, just multiply it, and format the formula cell as custom: [h]:mm to enable rollover of results exceeding 24 hours. Eg if you have in A1: 12:00, you could have in B1: =870%*A1, with B1 formatted as custom: [h]:mm. B1 will return: 104:24 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jay" wrote: > Hi I would like to know how to get percent of time? i.e. 30% of...

can't change the data type or field size
I am creating/editing updating fields in table design. I am trying to change the field size of the first field. But getting message.. 'You can't change the data type or field size of this field, it is part of on or more relationships. I deleted the relationship and still get the message. Can I screw anything up if I delete any other relationships. Or does access re-connect all relationships on demand. Thanks for any replies. On Sat, 9 Jan 2010 17:12:01 -0800, Steve Stad <SteveStad@discussions.microsoft.com> wrote: >I am creating/editing updating field...

Two digit day only
Query 1 has some code that calculates a due date - Date: IIf([tr.....[ReceivedDate]+30). Query 2 has only "Date" from query 1. I create a report with query 1 and the dates are fine. I use that report as a sub report and it only shows dates that had a two digit date. Example: 12/23/07 would carry over fine, but 12/04/07 does not display on the report when used as a sub report. I am posting this question to both sobjects Reports and queries because I'm not sure which are to solve the issue. Thank you Answered in m.p.a.reports ...

Set begin/end times in a bar chart
I am trying to set up a employee shift schedule with a floating bar chart for approx. 30 employees. I can get the bar to start at the right time, but it does not end at the correct time. How can I accomplish this? I have to print out a daily schedule on a weekly basis so I would like to create a reusable chart for this function. I am using Excel and Powerpoint. Thanks for your help. Hi, Have a read of Jon's article on Gantt charts. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343 He also has a collection of links on the subject. http://peltiertech.com/Excel/Cha...