How to summarize 30mins interval raw data to Daily Data

Sorry I could not download from dropbox.
Could you upload here? Click Advance then move to below part then click to "Attachment" button

Untitled.png

Try array-entering the following formulas in D5, E5 etc.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Modify the annual summary file to reflect years.

Hi FlameRetired,

Thank you for responding.

It seems the first formula you gave for Total/Sum is working but the rest are not working exactly to what I needed.

The formula you gave for Monthly Max was calculated based on the Max 30mins data not to the daily Max data, that is why it is giving me a value of 15.75 (max of Jan 13 7:30AM) instead of 38.6 which is the max total of Jan 10. same goes to Min and Average.

Please see attached.

That's odd. I had noticed the 38.6 in the upload. I applied Filtering to the January data and 15.75 was the MAX.

Are we using the same files?

Edit Never mind. I just re-read your last post.

I'll have to sleep on this one.

dedark

There is a slight inconsistency in the data. All days begin @ 12:00 AM except 1/1/2016. This throws the range offsets for each day off. May I insert a "fake" date / time above row 3 with a dummy rainfall of 0 to make the ranges consistent?

Also with helper column I get 40.39 as the daily MAX for Jan/2016 on 1/10/2016.

There are more dates missing from the data.

3/26/2016, 3/27/2016 and 3/31/2016.

Is this an oversight?

Never mind. This works.

There are two helper columns.

In column D of the Daily summary sheet this formula:

Formula:

Please Login or Register  to view this content.

It returns the daily totals at midnight for that day.

In column F this array-entered formula in Summary sheet.

Formula:

Please Login or Register  to view this content.

It calculates the actual number of days in the data for each month. It will be used to calculate the averages.

Then array-enter this formula in column B of Summary.

Formula:

Please Login or Register  to view this content.

for the monthly totals.

Array-enter this in column C for the MAX daily totals for each month.

Formula:

Please Login or Register  to view this content.

This one for the minimums in column D.

Formula:

Please Login or Register  to view this content.

This non-array formula in column E.

Formula:

Please Login or Register  to view this content.

Originally Posted by FlameRetired

dedark

There is a slight inconsistency in the data. All days begin @ 12:00 AM except 1/1/2016. This throws the range offsets for each day off. May I insert a "fake" date / time above row 3 with a dummy rainfall of 0 to make the ranges consistent?

Also with helper column I get 40.39 as the daily MAX for Jan/2016 on 1/10/2016.

Hi FlameRetired,

I just want to show my formula's progress. I have 2 formulas, one that has the helper and the other don't have.
The problem with the one that has the helper is that, this only works fine for the months that has 31 days, This will have an error in value on the Minimum and Averaging the data for months that has 28,29 or 30 days.
The other formula don't have the helper and it is calculating the values correctly on any month (thanks to FormR from http://www.mrexcel.com forum).This solved my Monthly formula.

I now have the Daily Summary and Monthly Summary.

What's left for me is the yearly summary. Do you have any idea with this based on the Monthly formula without the helper?

dedark,

I am confused by your last post. Did you see my last post #17 and workbook?

I believe those figures are correct. They agree with the sample in your recent upload. Have you tried Feb / Mar in the drop-downs? What am I missing?

Originally Posted by FlameRetired

Never mind. This works.

There are two helper columns.

In column D of the Daily summary sheet this formula:

Formula:

Please Login or Register  to view this content.

It returns the daily totals at midnight for that day.

In column F this array-entered formula in Summary sheet.

Formula:

Please Login or Register  to view this content.

It calculates the actual number of days in the data for each month. It will be used to calculate the averages.

Then array-enter this formula in column B of Summary.

Formula:

Please Login or Register  to view this content.

for the monthly totals.

Array-enter this in column C for the MAX daily totals for each month.

Formula:

Please Login or Register  to view this content.

This one for the minimums in column D.

Formula:

Please Login or Register  to view this content.

This non-array formula in column E.

Formula:

Please Login or Register  to view this content.

Hi FlameRetired, This works! the helper is consistent and it gives correct figures.

You also have a helper cells on the raw data sheet?

In column D of the Daily summary sheet this formula:

Formula:

Please Login or Register  to view this content.

It returns the daily totals at midnight for that day.

Is this necessary?

Anyways, the formulas work. Can I use this also to raw data that have more than one year data? if yes, how can I change my date reference from month alone (ex. January) to month and year (ex. January 2016) to represent January of 2014, 2015 or 2016? I have attached data of 2014-2016.

Originally Posted by FlameRetired

There are more dates missing from the data.

3/26/2016, 3/27/2016 and 3/31/2016.

Is this an oversight?

Hi FlameRetired,

Sometimes there were missing days or times because this raw data came from Automatic Weather Station (AWS) and there are times that the AWS will not respond due to hardware problem and takes days or two to fix.

miss-dreams,

I believe you may have posted to the wrong thread.

If you have a new question please start a new thread. From one of the sub-forums (General, Formulas & functions .... etc.) click on + Post New Thread button (upper left) and post your question.

Thank you,
Dave

With the gaps in the dates I can't think of a formula solution that wouldn't require that helper column.

Additionally the helper column saves the final formulas from having to recalculate those daily totals each time. Making the final formula do those calculations would also make the final formulas a nightmare to edit / debug.

I have been attempting an alternative using a SUBTOTAL / OFFSET formula that might simplify the whole operation and not require that helper. That looks promising,but the 'gaps' in dates causes problems I haven't found a work around for so far. I'll keep working on that.

Also a VBA solution might be better given the volumes of data. Unfortunately my VBA skills are not currently up to it.

Edit I've managed to work out the missing days issue without helpers. Do you want the monthly summaries for the given months in all the years or do you want it year specific? By the way the final formulas are going to be 6 to 7 lines long. Do you want me to continue?

Got it.

This works, but ...

it takes a long time to calculate, and ....

it uses the volatile functions OFFSET and INDIRECT.

If you aren't familiar with volatile functions they recalculate every time you edit / enter anything ... even if it has nothing to do with the formula ... ie edit a text header.

So if you change a header etc it takes the entire table about 20-25 seconds to recalculate on my machine.

With that much data the initial helper column takes a little longer, but when it's done it's done. Nothing in that model is volatile.

To shorten / simplify the formulas I used named ranges for the date data and rainfall data.

The date data in Name Manager I call DateDat

Formula:

Please Login or Register  to view this content.

the formula (months only) in B5 array entered is

Formula:

Please Login or Register  to view this content.

change SUM to MAX to MIN etc across.

The formula in H5 (month by year) is

Formula:

Please Login or Register  to view this content.

array-entered.

Originally Posted by FlameRetired

Edit I've managed to work out the missing days issue without helpers. Do you want the monthly summaries for the given months in all the years or do you want it year specific? By the way the final formulas are going to be 6 to 7 lines long. Do you want me to continue?

FlameRetired,

Yes please!. Sorry for the late reply. I was on to something that really consumes my time a lot lately, and I have not received email notifications of your reply here.

Anyway, it was not really what I intended to do (monthly summaries for the given months in all the years), I just want a monthly summaries to a year specific. but I really like the idea of finding also the average,max,min, etc... of the given month in all the years(ex. average total rainfall of January for 5 years or 10 years). have you done this already? 6 to 7 lines of formula don't matter to me.

Thank you so much.

I am starting to learn a bit in VBA and amazed by what it can do. I added a VBA print button to my sheet and a "save as image" button based on a given cell range and directly save to a given file destination.

Originally Posted by FlameRetired

Got it.

This works, but ...

it takes a long time to calculate, and ....

it uses the volatile functions OFFSET and INDIRECT.

If you aren't familiar with volatile functions they recalculate every time you edit / enter anything ... even if it has nothing to do with the formula ... ie edit a text header.

So if you change a header etc it takes the entire table about 20-25 seconds to recalculate on my machine.

With that much data the initial helper column takes a little longer, but when it's done it's done. Nothing in that model is volatile.

To shorten / simplify the formulas I used named ranges for the date data and rainfall data.

The date data in Name Manager I call DateDat

Formula:

Please Login or Register  to view this content.

the formula (months only) in B5 array entered is

Formula:

Please Login or Register  to view this content.

change SUM to MAX to MIN etc across.

The formula in H5 (month by year) is

Formula:

Please Login or Register  to view this content.

array-entered.

Yes. It really works!. I am already using this and made some modifications my self adding another helper to calculate other parameters correctly, and in naming name tags to shorten the formula as what you've shown. I also set the sheet to "manual calculate" under calculate options and made a VBA button to calculate manually so that it will not recalculate if not necessary.

Please see my file in this link (https://www.dropbox.com/s/3r5nm8ti80...d%29.xlsm?dl=0). I can't attached the file since it is more than 1mb in size. This file is linked to many sheets externally so this will result to error once recalculates. Just showing you my progress with your( and other people's) help. Thank you all for that.

What's left for me are the year specific summary and the one you've mentioned earlier (monthly summaries for the given months in all the years)

You are welcome. Thank you for the feedback and the rep.