Hourly Average With 15 Minute Data
Nov 6, 2009
I have a large sheet with the values of power from a counter in a 15 minute base.
A B
01.01.09 00:15 0,25
01.01.09 00:30 1,15
01.01.09 00:45 0,75
01.01.09 01:00 2,01
and son on until the end of the month
What I am trying to do is to create a macro which calculates the average per hour and put in another columns like this:
01.01.09 01:00 average from 00:15 until 01:00
01.01.09 02:00 average from 01:15 until 02:00
and so on until the end of the month.
View 9 Replies
ADVERTISEMENT
Sep 13, 2013
create a Macro that runs when system clock changes minute and then at every change of minute of system clock.
For example, if I give the execute command to start the macro at 09:14:45 (HH:MM:SS), then its first run should be only at 09:15:00, then next run at 09:16:00 so on...
I already have a Macro that runs every minute from initial run time, using
Code:
Application.OnTime Now + TimeValue("00:01:00"), "MyMacro"
but it seems to be unreliable, because in case I open another sheet that takes lets say 20 seconds to open, the next macro run is delayed by 20 seconds.
View 9 Replies
View Related
Nov 27, 2013
Entering a formula which will allow me to expand my data set by interpolating between sets of x,y values. My data set is in 5-minute resolution and I would like to interpolate so that I can output a new set of x,y values in 1-minute resolution. Here is my example of what I have and what I would like my end result to be, and also I have attached a sample of my data set in the below excel file:
Original data set
Date TimeTemperature oC
6/12/13 13:30 18.28
6/12/13 13:35 17.9
6/12/13 13:40 17.9
Desired data set
Date TimeTemperature oC
6/12/13 13:30 y
6/12/13 13:31 y
6/12/13 13:32 y
6/12/13 13:33 y
6/12/13 13:34 y
6/12/13 13:35 y
6/12/13 13:36 y
6/12/13 13:37 y
6/12/13 13:38 y
6/12/13 13:39 y
6/12/13 13:40y
datetime_temp.xlsx
View 2 Replies
View Related
Aug 22, 2007
I have a dataset in one column. Here 4 cells should be turned into 1 in a new column using a middelvalue.
How do i get excel to do this, so it uses 4 new cells from the first row to every new cell in the new column?
View 12 Replies
View Related
Jul 14, 2009
I have an excel work sheet with 2 columns and hundreds of rows. The first column is time in minutes over a number a years and the second is a list of corresponding data readings (numbers). I want to only select on the hour data from the list however I cannot seem to do it. The times vary in the sense that they are not all just 1 minute apart, some are every minute, then there might be a 10 minute gap in data, a few more minutes, then another gap etc.. All I want to be able to do is to select all the on the hour data from the list and its corresponding data value.
View 6 Replies
View Related
Nov 4, 2008
I have a list of days and hours, and data for whether a type of event either did or did not occur for each sampled hour. I need to return a new value, some sort of "Event Day" value, which will essentially tell me if an event occurred at least once for each day.
Attached is a sheet with 4 days' worth of data. Jan 1st and 3rd 2006 are my two "Event Days".
To complicate things, I can't be sure I have 24 hour readings for each day, so I can't assume each day is a data range (e.g. A1:A24). Hours might be missing. (As such, I am trying to organise along the lines of IF the preceding cell is the same date...)
I also have lots of data stored as months, so would love a formula I could just autofill down. All it needs to do is effectively flag up for me when a day has experienced one event.
View 4 Replies
View Related
May 8, 2014
I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form:
| Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.
View 3 Replies
View Related
Mar 14, 2013
I have a string of data containing hourly timestamps with a value attached to each timestamp.
I need the timestamps to be every 15th minutes instead of hourly. So basically, I need the hourly value to be continued four times for each timestamp.
See the attachecd workbook. It should be quite simple, but for some reason I just cant figure it out!? A bit embarressed In the woorkbook, I need VGTs and VPTs to be in a 15 minutes granularity.
ERROR: Cannot upload workbook - uploader not showing so here are shot of the raw data:
VGTs
VPTs
01/02/2013 00:00
[Code].....
View 9 Replies
View Related
Mar 26, 2013
I have large data sets with half hour values that I need to average into hourly values. Here is an example of the data:
28.12.2012
01:30:00
0,1
2,4
2,5
127,6
28.12.2012
02:00:00
0
0,9
0,9
118,5
[code]....
View 5 Replies
View Related
Feb 21, 2013
I have a worksheet containing one timestamp column and a single column of data, the interval being one minute. How can I aggregate it to get half-hourly totals?
View 4 Replies
View Related
Apr 13, 2012
I have got a lot of data to put in a chart that updates every 5 minutes with new data - i have a chart that Line chart that shows this.
It shows the cost of something every 5 minutes.
I have put this in a chart using offset but obviously i want to show the cost over the course of the day - because this updates every 5 minutes, the axis at the bottom (which has the time of the data) is massive and doesn't look good.
Is there a way to show the axis at an hourly interval but keep all the data in the chart?
View 5 Replies
View Related
Jan 11, 2014
In the attached excel file I'd like to use data that was polled every 15 minutes. i.e 00:15, 00:30, 00:45, etc. Can I do this without having to manually delete all the nonrequired data per each row?
Also I have many files of such data that and would like to combine the all them into one to represent data for the whole year polled at every 15 minutes.
View 2 Replies
View Related
Mar 2, 2008
I'm trying to create a macro to loop through daily one minute data.I believe the flowchart would be something like:
Create Variable
For each day in recordset
Loop through each minute record
Run system rules
Copy to Seperate worksheet
End
Additional Info:
Data is in columns B-G (Date,Time,Open,High,Low, Close)
Sample system could be something like:
If Current record close price is > Past 2 records Close Price Then
Buy 100 Shares
Liquidate if poistion moves against by 10%
Take Profit if position increases by 5%
Else close by days end
View 9 Replies
View Related
Feb 27, 2013
I have a spreadsheet that contains per minute data for the whole year. There are no gaps in data and it is complete with various values being zero. I cannot work out how to reduce the data into 30 minutes interval. I have tried to use Pivot Tables and grouping functions but it can give me daily data or hourly data but not per 30 minutes or 15 minutes of data.
What step I can follow to reduce the per minute data to 15 minute or 30 minute data.
Format of excel sheet is as below:
Date------ Time------ GSR
27/07/2010--0:00:00--1
27/07/2010--0:01:00--2
27/07/2010--0:02:00--0
27/07/2010--0:03:00--0
27/07/2010--0:04:00--1
27/07/2010--0:05:00--1
27/07/2010--0:06:00--1.5
27/07/2010--0:07:00--0
27/07/2010--0:08:00--0
27/07/2010--0:09:00--2
View 7 Replies
View Related
Jan 8, 2014
I have A:1 - X365 filled with hourly energy prices for 2013. I need to convert these to a single column - A1:A8760. I have tried writing some code and some macros with no success.
If I were to do it manually 365 times, the macro would look like this:
Copy Row A1:X1
select sheet 2
Paste Special > Transpose (fills A1:A24)
repeat for rows 2-365
Tried using this from a previous post, but it takes A1:A24 then B1:B24 and so on and makes them a single column - so it doesn't work unfortunately.
Sub MakeColumn()
Dim rng As Range
Dim LastRowSrc As Long
Dim LastRowDst As Long
Set rng = Worksheets("Sheet1").Range("A1")
[Code] .....
View 3 Replies
View Related
Feb 10, 2009
I have been using the attached formula to calculate total volume (customers seen) by hour (between 1 and 2, 2 and 3 and so on...). But how can I change this formula to take a census at a certain period of time (let's do it on the hour to be easy) instead of double-counting those who were there, but left in the same hour and those who just entered in the same hour. I hope this makes sense...a true census really should not count two people twice though which I think is happening here...
I hate to create a sense of urgency, but would love to begin working on this later tonight. Please let me know if you need more detail.
View 8 Replies
View Related
Mar 27, 2007
I am trying to create a conditional formula. I have multiple workers that work for different $$ per hour. I am keeping the hourly rates on one page for security purposes (the person that is going to be updating the hours worked is not very computer savy and they less they have to type the better) and the hours on another... I want copy a formula down the page that will calculate the hours to the hourly rate. The issue is that i want the formula to look at all of the hourly rates (assume they get raises) for Joe and multiply the most recent hourly rate times the hours... in addition I don't want the formula to re-calculate when a new hourly rate is put into the hours sheet.
Example
Jan. 1 joe works 8hours at $10 per hour formula in A1 (or wherever) calculates $80
Jan. 2 joe gets $1 raise
Jan. 2 Joe works 8 hours at $11 per hour formual in A2 calculates $88 but formula in A1 maintains the $80 and does not calculate the $1 raise.
View 12 Replies
View Related
Jun 2, 2007
I am trying to build a costings sheet for shift work. The first problem I am experiencing is getting excel to split up a shift into the different bands.
For example the shift needs to be calculated on different rate bands, from 00:00 to 08:00, 08:00 to 19:00 and then 19:00 to 00:00. There is a different hourly rate for each band. Therefore if I enter a start time and end time, I need excel to calculate the total charge over the different rate bands.
For example a shift 07:00 - 21:00 would have:
1 hour 07:00 - 08:00 at Night rate
11 hours 08:00 to 19:00 at Day Rate
2 hours 19:00 to 21:00 at Night Rate
Once I have this worked out I have the added complication of different rates on Friday evening,s, Saturdays and Sundays etc. But at the moment one problem at a time !
View 9 Replies
View Related
Mar 22, 2007
I have a list of values at 'x' min intervals (the intervals sometime changes). I want to sum each hour's values eg. between 0:0:0 hour and 0:59:59, I want to add values 1040+800+860+870. Then I want to proceed and add all values for the next hour i.e. between 1:0:0 and 1:59:59, and so on. I have about 65000 of these calculations to do, so the need for some vba programming.
Hour value
0:14:531040
0:29:53800
0:44:53860
0:59:53870
1:14:53850
1:29:53860
1:44:53710
1:59:53670
2:14:53610............
View 5 Replies
View Related
Sep 26, 2013
i have a s/sheet with thousands of times in a column (eg col1 below in 24 hour format) and would like these in an adjacent column in hourly intervals (eg in Col2 below
e.g.
Col1 ColB
00:23 0 - 1
02:54 2 - 3
09:07 9 - 10
15:24 15 - 16
Is there a formula I can run to enable?
View 2 Replies
View Related
Dec 22, 2009
1/1/20080:221/2/200814:051/1/20082:451/3/200819:301/1/20089:001/1/200819:33
In time Out time
I 've used the site many times in the past, but this is my first post.
Above is a small example of the data Im working with. I'm not wanting to plot all of this out by hand, so hopefully someone can help (there are around 30,000 samples). I'm looking to track by hour when a customer is actual here. The first row is one customer in @ 00:22 and out the next day at 14:05.
I would like to have dates of the year down column A and hours 0 through 23 across row 1. Then a number of customer here on 1/1/2008 by every hour of the day on to 1/2/2008 so on and so.
View 9 Replies
View Related
Apr 13, 2006
I have a macro that I want to run on an hourly basis, say at 9am, 10am, etc whenever the workbook is open. This needs to be definable as I have several users that will be conducting the same activity throughout the day. In other words I want the macro to run on the hour for user 1, quarter past the hour for user 2, half past for user 3 and quarter to for user 4. Each user does have their own unique macro to run so it should (hopefully) just mean introducing a line of code to each, defining the time to run.
View 4 Replies
View Related
Mar 24, 2008
How can I get the median of a column that contains both hourly and annual rates? I would like to either multiply the hourly rate * 2080 or divide the annual rate by 2080 then get the median.
View 6 Replies
View Related
Jan 8, 2008
Is there a formula that will calculate standard 60 minute time in to 100 dinute time? Example 2:21 = 2:35. I know how to get to the answer with a calculator, but would like a formula to cut out all of the extra steps.
View 3 Replies
View Related
Apr 18, 2006
I am trying to calculate earnings based upon hours worked based on 24 hours format (time out - time in = time worked) multiplied by hourly rate. I cannot get the proper calculation. How can I attach a copy of the Excel worksheet?
View 9 Replies
View Related
Dec 6, 2013
I'm trying to use some call data for some predictive staffing. I need to see the highest number of calls happening around the same time each day. I'll have to do this for a number of different lines, so working out a formula that could apply.
I have two columns, date and time stamps of the calls. A small sample is below.
7/1/13 5:50 AM
7/1/13 9:49 AM
7/1/13 10:33 AM
7/1/13 10:53 AM
7/1/13 11:19 AM
7/1/13 11:41 AM
7/1/13 12:07 PM
7/1/13 3:58 PM
7/1/13 4:03 PM
7/1/13 5:35 PM
7/1/13 6:27 PM
7/1/13 6:39 PM
7/1/13 7:37 PM
View 2 Replies
View Related
Mar 6, 2014
What is the formula for taking two times, and finding the minutes between them. E.g. 12:35 PM-12:28 PM=7. I know i can use (A1-A2)*1440, but is there an easier way?
View 4 Replies
View Related
Dec 17, 2012
I currently have a macro that runs every 10 seconds. This macro calls 5 sub-routines.
Code:
Public Sub Copy_Data()
*****code****
*****code****
*****code****
*****code****
Call SortData
Call Create_OutOfStock_File
Call Create_NearEmpty_File
Call Create_InStock_File
Call Create_Other_File
Application.OnTime Now + TimeValue("00:00:10"), "Copy_Data"
End Sub
I need to keep the current code to run Copy_Data every 10 seconds, however, i would like:
Code:
Call Create_OutOfStock_File
Call Create_NearEmpty_File
Call Create_InStock_File
Call Create_Other_File
To be called/run every odd minute, for example, 00:01:00, 00:03:00, 00:05:00
View 6 Replies
View Related
Mar 21, 2013
Is there a way to refresh a sheet every minute? So not on selection change but automatically every minute
I know this code will refresh but i dont know how to do this every minute (Not a selection change as then this could refresh every second which will not be efficient)
Sheets("Notes").Calculate
View 1 Replies
View Related
May 23, 2008
i am using excel 2007 on windows xp. here is what i have, i have 5 scanners that i want to track to see how much they scan per minute in a day, week, and month period. here are my data in excel for one scanner in a 8 hour shift:
500047022059574
A1 = the image they scan for a day
B1 = hours they worked that day (cell formatted to [m])
C1 = A1/B1
it should come out to 10.63 but it doesnt.
View 9 Replies
View Related
