+ Reply to Thread
Results 1 to 2 of 2

Trying to calculate daily averages and seasonal averages

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    germany
    MS-Off Ver
    2011
    Posts
    1

    Question Trying to calculate daily averages and seasonal averages

    Hello,

    I have a data set of water temperatures in a river which were taken every 15 minutes for a year. I'm trying to calculate the average water temperature for each day of the year. After that, I would like to calculate seasonal averages.

    I have tried to use formulas, but they didn't work for me, and a pivot table but it doesn't seem to be working either. I am not experienced enough to figure out what isn't working and why. Any advice will be greatly appreciated!

    P.S. I have also tried to format the dates to be dates instead of text but it didn't work. Is it a problem?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Trying to calculate daily averages and seasonal averages

    Your problem is that your dates are strings that look like dates, and your temperature data has extra spaces at the end.

    Clean up your data by creating two new columns: in C1, enter the header "Date" and in C2, enter the formula

    =DATE(2016,MID(A2,4,2),LEFT(A2,2))

    and in D1, enter the header "Temperature" and in D2, enter the formula

    =IFERROR(VALUE(SUBSTITUTE(B2,CHAR(160),"")),"")

    Then copy both those down to match your data and use columns C and D as the basis of your pivot tables - use Date as the row source and temperature as the data source, set to average.
    Bernie Deitrick
    Excel MVP 2000-2010

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 03-13-2015, 05:34 AM
  2. Calculate and compare rolling averages with daily data entry?
    By iamchipdouglas in forum Excel General
    Replies: 8
    Last Post: 12-26-2014, 01:31 PM
  3. Replies: 3
    Last Post: 10-01-2013, 05:05 AM
  4. Calculate monthly, quarterly, and yearly averages from daily data
    By jhound in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2013, 11:16 AM
  5. Need to calculate monthly averages from daily values
    By smiso24 in forum Excel General
    Replies: 2
    Last Post: 07-13-2011, 01:03 PM
  6. Formula to calculate daily averages
    By jmiller7 in forum Excel General
    Replies: 1
    Last Post: 01-19-2011, 12:13 PM
  7. Replies: 1
    Last Post: 05-02-2008, 10:41 AM

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