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.