+ Reply to Thread
Results 1 to 14 of 14

Need formula to average daily data into month and year data

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007, 2013
    Posts
    26

    Need formula to average daily data into month and year data

    I want it to average based on month and year. I have daily data and want to average everything for say January 2000 into one cell and February of 2000 and so on. Column A has date (mm/dd/yy) and column B has data.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Need formula to average daily data into month and year data

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Need formula to average daily data into month and year data

    You can try this:

    For January: =AVERAGEIFS(B:B,A:A,">=1/1/2000",A:A,"<=1/31/2000")
    For February: =AVERAGEIFS(B:B,A:A,">=2/1/2000",A:A,"<=2/29/2000")
    etc...

    For the year: =AVERAGEIFS(B:B,A:A,">=1/1/2000",A:A,"<=12/31/2000")

    - Moo

  4. #4
    Registered User
    Join Date
    01-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007, 2013
    Posts
    26

    Re: Need formula to average daily data into month and year data

    Discharge example.xlsx

    Here is the example. I want the formula to live on the second tab.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Need formula to average daily data into month and year data

    You can use a Pivot Table to achieve this

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Need formula to average daily data into month and year data

    Here is a Pivot Table example as Pepe suggested - see attached file

    - Moo
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007, 2013
    Posts
    26

    Re: Need formula to average daily data into month and year data

    Not familiar with pivot tables. Can you walk me through how you did that?

  8. #8
    Registered User
    Join Date
    01-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007, 2013
    Posts
    26

    Re: Need formula to average daily data into month and year data

    I'm not familiar with pivot table. Can you walk me through how you did that?

  9. #9
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Need formula to average daily data into month and year data

    Do this:
    1. Select all data from Sheet1 (A1:B732)
    2. Select the 'Insert' tab on the toolbar, then click on 'PivotTable'
    3. In the section 'Choose where you want the PivotTable report to be placed', click the 'Existing Worksheet' radio button, then select the 'Spring Branch Monthly Median' tab and select cell E2 then click OK.
    4. A 'PivotTable Field List' should appear to the right side of your screen. Drag the 'Date' field into the 'Row Labels' section, and drag the 'Discharge...' field into the 'Values' section.

      You should now see that the PivotTable displays the data for every date in the data set... we're going to fix that in a second...

    5. In the 'Values' section, click the black downward pointing arrow at the right side of where it says "Sum of Discharge..." and select 'Value Field Settings'
    6. In the window that pops up, where it says 'Summarize value field by', select 'Average' then click OK

      You'll see that nothing has really changed yet, except it now says Average... instead of Sum... Here's where we average it by month and year:

    7. Right-click the cell with 1/1/00 in it (should be E3) and select 'Group..."
    8. Make sure the 'Starting At' and 'Ending At' checkboxes are checked and the dates should be 1/1/2000 for 'Starting', and 12/31/2001 for 'Ending'
    9. Finally, select the 'Months' option and then hold down CTRL and select the 'Years' option as well, so both are selected. Then click OK.

    What you SHOULD have now is a PivotTable with Averages grouped by year, and within each year, by month. Feel free to format to as many or as few decimal places as you wish.

    If you have any other questions, I will try to answer them as I have time. =)

    - Moo

  10. #10
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Need formula to average daily data into month and year data

    And there is also a lot of explanations at http://www.contextures.com/tiptech.html#Go_P

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Need formula to average daily data into month and year data

    And another place to look is in my signature, click on the link to Excel Video Tutorials.

  12. #12
    Registered User
    Join Date
    01-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007, 2013
    Posts
    26

    Re: Need formula to average daily data into month and year data

    You guys have been a lot of help. I've noticed that there isn't a median option in the pivot table. Am I missing something or am I going to have to do it as Moo suggested in post #3?

  13. #13
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Need formula to average daily data into month and year data

    You can use this ARRAY formula to derive the MEDIAN by month from your data (Pivot Tables don't do MEDIAN as far as I know):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Click on cell C2 of the 'Spring Branch Monthly Median' sheet, and paste the above formula into the formula bar, then press CTRL + SHIFT + ENTER, instead of just enter, so that it applies the array formula.

    You can then just drag-fill that down for the other months.

    - Moo

  14. #14
    Registered User
    Join Date
    01-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007, 2013
    Posts
    26

    Re: Need formula to average daily data into month and year data

    That worked great. Thanks.

+ 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. Converting Half-hourly data into average daily data
    By TommyHayes1989 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-24-2018, 11:19 AM
  2. Average daily data by month
    By aussie_sox in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2015, 12:48 PM
  3. Month to Date and Quarter to Date Average from Daily data
    By asvanthi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2014, 07:10 AM
  4. Converting monthly data into average daily data
    By milkkbone in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-16-2013, 05:25 AM
  5. Average and Median by week and month from daily data
    By jjj444 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2013, 11:01 PM

Tags for this Thread

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