Question regarding summing up columns.

[espc]

Hello everyone, stumbled across this site while trying to solve an Excel dilemma. I have a set of wind data that records every half an hour, I am trying to average the wind speed data between the periods 00:00 AM - 6:00 AM , 6:00AM-15:00 PM, 16:00PM-23PM and if possible send the results in different worksheets but that is not crucial. I tried working with the if statement but I cant make get it to recognize the time, the time is in this format "1/1/2010 0:00". Any pointers with this problem will be greatly appreciated, I am still familiarizing myself with the rules of this forum and if I have broken any of them I apologize in advance.

Kindest regards,

[Spencer101]

Hi espc,

Could you post some example data and your expected results?

It's far easier to help you with a solution that way

S.

[espc]

I have attached a sample of the wind data I am using, I am trying to calculate the average between the hours:
00:00 AM - 6:00 AM
6:00AM-15:00 PM
16:00PM-23PM
With the resulting averages being sent to a new worksheet or different columns. I have been trying for quite a while but cannot seem to be able to get the correct function that is used. Thank you again for your help.

Kindest regards,

[judgeh59]

so I added a helper column and you can look at the formula then I created a pivot table and grouped by days....I didn't have to verify everything, but check it out....HTH

[judgeh59]

sorry....sometimes I leave out words....I didn't have TIME to verify everything....HTH

[espc]

Hi , thank you for your reply. I could follow the formula that you used to separate them into morning/day/night but unfortunately I could not see how you got the tables on the first worksheet. The data that you put on the first worksheet is exactly what I need , thank you very much for taking the time to help me out.


Kindest regards,

[PietBom]

I tried to solve it with SUMPRODUCT formula and 2 helper columns.
And check if with a pivottable.

[espc]

Thank you for your help, I apologize but could you also show me a way to get a column for each 3 different periods ? I am trying to plot a histogram to show the frequency of the wind speeds in each period. Again thank you very much for your help , I have learned quite a bit from this.

Kindest regards,

[PietBom]

espc,

I added a table and a graph.

Thinking this is what you are looking for

[judgeh59]

sorry it took so long to respond....I actually had to do real work....so the pivot is generated like this....Rows - More Help (column, not a very good one).....column lablels (DTG - Stands for Date, time, group) and the Value - Average of Data....then right click on a date in the columns and you'll see "Group".....from there you and group this dates into just about anything....if you want to do weeks, click on days and choose 7 days.....have fun

[judgeh59]

just a thought.....forum ettiquette suggests changing the status to "Solved" if this issue has been resolved.....you can do that clicking on the "thread tools" at the top of the post....also, giving stars to those who helps is a polite thing to do....when you help people, hopefully they'll give you a star.....

[espc]

I apologise for that , I have been away for the past few days. Thank you very much for your help in solving this.

[judgeh59]

no problem....glad I could help.