Fringedweller
New Member
- Joined
- Jan 21, 2018
- Messages
- 6
I am hoping that someone can help me with a macro to average data from multiple time points
Simply i have a 4 columns Day, Date, Time, Data
The same format appears on each worksheet with in the workbook which i need sort and Average over the data range
The data sort should be by date (ascending) then by Time (Ascending)
Time is based on a 24 Hour period data points can arrive at any point during this period
I would like to add that i have tried to do it my self using the inbuilt sort functions but can no seem to get it to work across multiple sheets
also i got lost in the Pivot table grouping options
Cell range on all worksheets is Range("A2:D500") Includes headers A2-D2
I guess the output columns would be F2:G500 F=Time(whole Hour, example 1am, 2am, 3am ..etc) g= averaged data from previous time until current time
Then i can Graph the averages of a week based on date and hourly intervals for each worksheeet
my goal is then combine all worksheets into a single graph using each worksheets title as labels
example of data
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>I hope i have given enough information for someone to assist..
Cheers... any other questions please ask
Simply i have a 4 columns Day, Date, Time, Data
The same format appears on each worksheet with in the workbook which i need sort and Average over the data range
The data sort should be by date (ascending) then by Time (Ascending)
Time is based on a 24 Hour period data points can arrive at any point during this period
I would like to add that i have tried to do it my self using the inbuilt sort functions but can no seem to get it to work across multiple sheets
also i got lost in the Pivot table grouping options
Cell range on all worksheets is Range("A2:D500") Includes headers A2-D2
I guess the output columns would be F2:G500 F=Time(whole Hour, example 1am, 2am, 3am ..etc) g= averaged data from previous time until current time
Then i can Graph the averages of a week based on date and hourly intervals for each worksheeet
my goal is then combine all worksheets into a single graph using each worksheets title as labels
example of data
Day | Date | Time | Data |
Sat | 20 | 23:37:29 | 1.2 |
Sat | 20 | 23:27:46 | 1.2 |
Sat | 20 | 20:45:09 | 1.2 |
Sat | 20 | 20:41:54 | 1.7 |
Sat | 20 | 20:38:39 | 2.3 |
Sat | 20 | 20:35:24 | 1.9 |
Sat | 20 | 18:21:51 | 1.4 |
Sat | 20 | 12:32:57 | 1.2 |
Sat | 20 | 12:29:42 | 1.4 |
Fri | 19 | 21:14:29 | 1.2 |
Fri | 19 | 17:20:28 | 1.2 |
Fri | 19 | 17:17:14 | 1.3 |
Fri | 19 | 17:13:57 | 1.2 |
Fri | 19 | 5:39:40 | 1.3 |
Thu | 18 | 4:09:20 | 1.2 |
Wed | 17 | 15:50:40 | 1.3 |
Wed | 17 | 13:21:03 | 1.3 |
Wed | 17 | 0:19:00 | 1.4 |
Wed | 17 | 0:15:49 | 1.5 |
Wed | 17 | 0:12:38 | 1.3 |
Mon | 15 | 20:32:29 | 1.4 |
Mon | 15 | 6:41:32 | 1.2 |
Mon | 15 | 6:38:19 | 1.3 |
Sun | 14 | 14:12:30 | 1.2 |
Sun | 14 | 14:09:15 | 1.4 |
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>
Cheers... any other questions please ask