Guybrush

Alright, this thread may be slightly inappropriate on MB, but since we already have several topics going on fetishes, I thought "why not"? To those who don't like it, I'm sorry - I wouldn't post here if I wasn't getting slightly desperate (I'm also looking for help on other forums) and the problem is this :


I have a problem with a data set that I'm working on. Basically, I've got a data that was downloaded from a weather station. I need weather data like average temperature, maximum temperature, minimum temperature for each day over a 90 days period. This is simple enough. The problem, however, is that the weatherstation has been sampling every 5 minutes. In the data sheet, each day is represented by 12*24 rows of data (12 samples per hour times 24 hours). The total dataset across all days is something close to 26000 rows!

Of course, I can spend hours doing all this by manually by average(), min() and max() and then selecting the cells I want it to apply to each time, but the thought of doing so makes me wince. I would have to do it 90 times per column across hundreds of cells each time and I'm after much more than just temperatures.

Can any of you guys help me? Do you know of a less labour intensive way of doing this?

edit :

Here I've illustrated the problem so you can get a rough idea of what I'm up against.

• Time - Shows the time the sample was taken. Notice that it's every 5 minutes.
• BP_mBar: BREINOSA (hPa) - Shows weather data (here, pressure) sampled every five minutes.
• DATE - Shows dates, I need the data on the left for each day rather than every five minutes
• AVERAGE BP_mBAR pr. Day - Average mBar per day, what I basically need excel to calculate

The coloured columns are not part of the original data set, but are added by me. As you can see, the statistics are not hard. The picture shows me editing the contents of cell E2. I would of course need to repeat the same process for every day. It's just insanely labour intensive and I'm hoping to find a way which makes this a little easier.

I already tried macroing the process, but then it ended up calculating the same numbers for every day. In other words, all the dates then got the same numbers.

__________________
Something Completely Different

Thrice

Can you please explain why your method wont work? I might be missing something, but can you not just =AVERAGE(B2:B288) like you did, also for MIN and MAX, but only have to do it 90 times?

__________________


Lew Harrison, who looked like an anarchist with his red eyes and fierce black beard, had been writing furiously in one corner of the room. "That's good—happiness by the kilowatt," he said. "Buy your happiness the way you buy light."

Guybrush

Yes, I could do that .. It's just that it's so horribly labour intensive, I want to find a way to automate this process. Otherwise, it's gonna take me hours and hours.

This may look like it's something I "only" have to do 90 times, but there's many more columns so we're talking about several hundreds

edit :

The raw data set is here by the way -> sv

__________________
Something Completely Different

Seltzer

You could use Excel VBA to write a macro to do it, rather than 'visually' recording a macro - e.g. if you click edit for the macro you've created already, Excel will show you the VBA code behind it.

Basically you want an expression that will calculate the averages of the cell ranges (B2 + 288i) : (B288 + 288i) and output them to cells (E2 + i), where 24*12 = 288 is the offset of cells from time x at one day to time x on the next day, and i is a number which starts at 0 and increments until it reaches 90.

This can be achieved in programmer terms by using a FOR loop. Unfortunately I've never used Excel VBA so I don't know the syntax that you would use to create a for loop, select ranges of cells, use Excel functions, alter cells etc. so I can only steer you in the right direction. I've got loads of assignments to do atm, but if you can wait until later today, I could learn a bit of VBA and figure out how to do it.

__________________

Guybrush

Yes! Something like this is exactly what I need. Seltzer, you may just be a life saver!

I thought if there was a way to use IF-conditions in Excel (I guess there might be?), then perhaps I could also use that to define the days rather than a 24*12 offset.

__________________
Something Completely Different

Guybrush

Yay!

I managed to figure this out. It was actually much easier than I feared by the use of pivot tables. For anyone who have to tackle large amounts of data in excel, check out a tutorial on pivot tables .. It will help you.

__________________
Something Completely Different

Seltzer

Nice.

I just submitted my last assignment 2 mins ago, so I was about to ask how you were getting on.

__________________