Deriving daily maximum temperature from dataset with gaps

[jones1234]

Hi all,

I have a dataset with hourly temperature and wind speed/direction data. I want to calculate for each day separately the daily maximum temperature and the mean wind speed/direction. I tried to derive these values by selecting every other 24 cells. However, there are multiple data gaps scattered in the dataset and I don't know how to work around that. Is there a more efficient solution to manually find every data gap and correct for it? They do not necessarily need to be filled.

Thanks

[Pete_UK]

There are no headers in your file, so which columns represent which data?

How would you calculate the average wind speed and direction? A straight average of the values in each column?

Pete

[jones1234]

Hi Pete,

Thanks for the answer. The first column is the date (A), then the temperature (B), wind speed (C) and wind direction (D).
I tried the following for the maximum temperature per day: =MAX(OFFSET($B$1,(ROW()-ROW($J$1))*24,,24,)).
This however requires the data to be complete such that every day has 24 measurements. This is however not the case, as there are multiple data gaps.

[jones1234]

There are no headers in your file, so which columns represent which data?

How would you calculate the average wind speed and direction? A straight average of the values in each column?

Pete

Hi Pete,

Thanks for the answer. The first column is the date (A), then the temperature (B), wind speed (C) and wind direction (D).
I tried the following for the maximum temperature per day: =MAX(OFFSET($B$1,(ROW()-ROW($J$1))*24,,24,)).
This however requires the data to be complete such that every day has 24 measurements. This is however not the case, as there are multiple data gaps.

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[ardross]

Maybe this???



=SUM(INDEX(B$2:B$34000,1+24*(ROWS(G$2:G2)-1)):INDEX(B$2:B$34000,24+24*(ROWS(G$2:G2)-1)))/COUNTIF(INDEX(B$2:B$34000,1+24*(ROWS(G$1:G1)-1)):INDEX(B$2:B$34000,24+24*(ROWS(G$2:G2)-1)),"<>")

copied across and down.

[jones1234]

Hi there,
Thanks for the reply! If I, however, copy this all the way down, the date does not stop at 30/11/20 (where the actual data list stops) but at 10/11/2020...

Attachment 782648

[JeteMc]

Selecting attachment 782648 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
That said and using the file attached to ardross' post (#6):
1. Add a helper column (E) to display only the dates using: =DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))
2. The Max Temp column is populated using**: =MAX(IF(E$2:E$33835=G2,B$2:B$33835))
3. The average wind speed and wind direction columns are populated using**: =AVERAGE(IF($E$2:$E$33835=$G2,C$2:C$33835))
** Denotes array entered formulas which are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

[jones1234]

Selecting attachment 782648 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
That said and using the file attached to ardross' post (#6):
1. Add a helper column (E) to display only the dates using: =DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))
2. The Max Temp column is populated using**: =MAX(IF(E$2:E$33835=G2,B$2:B$33835))
3. The average wind speed and wind direction columns are populated using**: =AVERAGE(IF($E$2:$E$33835=$G2,C$2:C$33835))
** Denotes array entered formulas which are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

Thank you! This really helped alot.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.