+ Reply to Thread
Results 1 to 10 of 10

Summarising Data in Excel

  1. #1
    Registered User
    Join Date
    07-13-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    4

    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
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    1,775

    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.
    Add as many columns as needed. Load to data model directly.

    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.
    Attached Files Attached Files
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    2010
    Posts
    3,424

    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
    Attached Files Attached Files
    Please show appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  4. #4
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    798

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-13-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    4

    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. #6
    Registered User
    Join Date
    07-13-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    4

    Re: Summarising Data in Excel

    Thanks Ranman, will try the macro approach.

  7. #7
    Registered User
    Join Date
    07-13-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    4

    Re: Summarising Data in Excel

    Thanks for the effort CK76. Am still working to see how it goes.
    Last edited by jmasanganise; 12-07-2017 at 04:55 AM.

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    2010
    Posts
    3,424

    Re: Summarising Data in Excel

    Quote Originally Posted by jmasanganise View Post
    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. #9
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - 2013
    Posts
    6,726

    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: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave

  10. #10
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - 2013
    Posts
    6,726

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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