+ Reply to Thread
Results 1 to 3 of 3

average daily values from 30 min interval data

  1. #1
    Registered User
    Join Date
    08-03-2010
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2003
    Posts
    1

    average daily values from 30 min interval data

    Hi

    I would be very grateful if any one could help me. I have several data sets, showing readings from 30 different sites at 30 min intervals for an intire year. i would like to work out daily averages if possible.

    Each data set is made up as follows:

    Column A is the date in the format d-mmm-yy hh:mm:ss - eg a day will start 01-Jan-10 00:00:00 and end 01-Jan-10 23:30:00

    Column's B to AE are 30 different sites, labelled 1-30 and have 2 decimal place readings eg 0.05

    is it possible to work out daily averages for each column?

    I have tried sumif/countif but found it difficult due to the number of columns.

    Many thanks
    Rach

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: average daily values from 30 min interval data

    So each row is represents just 1 day?

    Isnt this simply

    =AVERAGE(B1:AE1)

    copied down a spare column?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: average daily values from 30 min interval data

    Daily Averages on a Site by Site basis or across all sites simultaneously ?

    In theory - each average range will be 24 rows high - the number of columns in the range will depend upon the answer to the above (ie either 1 or 30)

    However, either way I would suggest you use a Pivot Table.

    For a site by site daily average have Date Column as Row Field and each Site Column as a Data Field - each set to Average.

    Once set up Group the Date field by Day, Month & Year and you will get your desired output.

    If you want an Average across all sites (ie 1 avg per day) then add a column at source (AF) which Averages B:AE .. use this new column as the Data Field in the Pivot (set to Average)

    Pivots are purpose built for this type of analysis - are very straightforward to configure yet very very powerful reporting tools.

+ 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