# Summarising Data in Excel

1. ## Summarising Data in Excel

Hi

I am working with huge amounts of climate data. I have rainfall data recorded half hourly from 1 August 2017 to 9 October 2017 and I used a pivot table to obtain daily values (see attachment). The daily totals are accumulated from 12 A.M. of one day to 12 A.M. of the following day which is fine. I would like to obtain daily totals accumulated from any other time interval which defines a 24-hour period other than 12 Am -12 AM e.g. 5 A.M. to 5 A.M. of the following
day. I further want to obtain daily totals accumulated during day time only e.g. from 6 A.M. to 6 P.M. of the same day. My question is: how can I use excel to do that?

Joseph

2. ## Re: Summarising Data in Excel

Personally, I'd load data to PowerQuery/Get & Transform and do grouping calculation on additional column(s).

Ex. For 5 Am to 4:59:59 AM
``Please Login or Register  to view this content.``

Then, you can switch row label field as needed to check from one to the other (or have multiple pivots, one for each grouping).

See attached sample.

3. ## Re: Summarising Data in Excel

You could do it with formulas. See attached.
Change the times in the yellow cells to dictate at what time a 24 hour period should start/end.
Change the times in the green cells to specify a start and end time.

Hope that helps.

BSB

4. ## Re: Summarising Data in Excel

Attached is your file with a macro,
to enter the hour and it will build blocks.
These can be put in pivot table now.

5. ## Re: Summarising Data in Excel

Your template works BadlySpelledBuoy, fantastic! Thank you so much. Suppose column B (Raintotal) was Average Temperature and I wanted to obtain daily averages in a similar way, can I replace SUMIFS with AVERAGEIFS in the logical functions?

6. ## Re: Summarising Data in Excel

Thanks Ranman, will try the macro approach.

7. ## Re: Summarising Data in Excel

Thanks for the effort CK76. Am still working to see how it goes.

8. ## Re: Summarising Data in Excel

Originally Posted by jmasanganise
Your template works BadlySpelledBuoy, fantastic! Thank you so much. Suppose column B (Raintotal) was Average Temperature and I wanted to obtain daily averages in a similar way, can I replace SUMIFS with AVERAGEIFS in the logical functions?
Happy to help.

As for calculating avg. temps, changing SUMIFS for AVERAGEIFS would work indeed.

BSB

9. ## Re: Summarising Data in Excel

Late to the party.

Here's another way building ranges.

Change the start times in row 3; change the half hour increments in row 4.

This formula in L5 filled down and across for SUMS. If you wish change to AVERAGE with the range selected and while in edit mode hold down just Ctrl while hitting Enter.
Formula:
`Please Login or Register  to view this content.`

10. ## Re: Summarising Data in Excel

jmasanganise

There is missing data in the example: eg. rows 1789:1790 go from 9/7/2017 5:30 to 9/7/2017 9:30. This will throw time intervals off.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1