Reduce 1 minute data to 30 minute data in Excel

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results, too.

1) Put the first timevalue in cell L2

2) Enter the formula shown in L3, and copy L3 downward as far as needed.
=INT(L2) + TIME(HOUR(L2), MINUTE(L2)+30, 0)

3) Enter the formula shown in M2 and copy across the table:
=AVERAGE(OFFSET(INDEX(B:B, MATCH($L2,$A:$A, 0)), -5, , 6, 1))

Insert a helper column between columns B and C and format as general. Enter this formula in C21 and copy down the length of your data:

Formula:

Please Login or Register  to view this content.

This will insert a 1 for minutes greater than 0 and less than or equal to 30 and a 2 for all other minutes.

Click on the Timestamp cell and click on the Data Tab, Subtotals.

Select At each change in Helper, Use Function AVERAGE. In the Add Subtotal to, select everything except Timestamp. Select Summary Below Data and click OK.

Now you will have controls to the left of the data. Click on each one under 2 (second column of controls) and the report that you gave as an example is produced.

This is an attempt to copy the results into the answer.

TIMESTAMP Helper WindSpeed WindDirect Air_temperature RH Pyranometer_Avg Reference1_Avg Reference2_Avg Month Day
1 Average 1 4.25 177.0666667 9.6 71.11666667 548.8333333 515.1666667 494 12 11
2 Average 2 3.883333333 171.0833333 9.666666667 71.41666667 482.6666667 452.1666667 599.3333333 12 11
Grand Average 1.5 4.066666667 174.075 9.633333333 71.26666667 515.75 483.6666667 546.6666667 12 11

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

This thread is 9 years old.

It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!


The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.