+ Reply to Thread
Results 1 to 9 of 9

How to calculate average by month and year

  1. #1
    Registered User
    Join Date
    10-16-2014
    Location
    Vancouver, WA
    MS-Off Ver
    2010
    Posts
    29

    How to calculate average by month and year

    I was attempting to get help with this in Chart and Pivot table section, but I may have gotten ahead of myself. I need help with a formula to calculate the average by month and year for each customer on the list. I have enclosed a worksheet. Under Option tab, I began attempting to gather info. I figured out how to calculate the entire total for each customer using



    =IFERROR(AVERAGEIF(RunningLog!B:B,Option!G1,RunningLog!C:C),0)



    But cannot figure out how to break it out by month and year. Any assistance would be greatly appreciated. Thank you.
    Calculate by MMMYYY.xlsx

    MMMYYYAVG.jpg
    Last edited by 1losthuman; 04-01-2015 at 08:59 PM.

  2. #2
    Registered User
    Join Date
    12-14-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    20

    Re: How to calculate average by month and year

    Would adding a month column using month(F1) and a year column using year(f1) help?

  3. #3
    Registered User
    Join Date
    10-16-2014
    Location
    Vancouver, WA
    MS-Off Ver
    2010
    Posts
    29

    Re: How to calculate average by month and year

    Quote Originally Posted by dandungan View Post
    Would adding a month column using month(F1) and a year column using year(f1) help?
    I do not understand... I am still a "rookie" at this stuff.... can you elaborate?

  4. #4
    Registered User
    Join Date
    12-14-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    20

    Re: How to calculate average by month and year

    First, I added a month and year column to your RunningLog using the month and year functions and copied them down to the last row.

    Then, in the Option sheet I changed your Start and End dates to month and year. I filled those in for 2014. I had to change the format to "General".

    Then, I used the averageifs function that allows more criteria.

    Check out the attachment and see if it makes sense.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-16-2014
    Location
    Vancouver, WA
    MS-Off Ver
    2010
    Posts
    29

    Re: How to calculate average by month and year

    Thank you for the explanation. I understand now. I think this will work.

  6. #6
    Registered User
    Join Date
    12-14-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    20

    Re: How to calculate average by month and year

    Good! Post back if there are other issues. I'm no expert, and I'm learning from this great forum, as well.

    If this worked for you, rate the thread and mark it solved, ok?

    Thanks,

    Dan

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to calculate average by month and year

    You dont need the "End" column or any helpers, try this instead...

    G2=IFERROR(AVERAGEIFS(RunningLog!$C$2:$C$1392,RunningLog!$A$2:$A$1392,">="&$E2,RunningLog!$A$2:$A$1392,"<="&EOMONTH($E2,0),RunningLog!$B$2:$B$1392,Option!G$1),"")
    copied down and across

    For the Year (12 months from the 1st start date?), change the boled part from 0 to 12...
    =IFERROR(AVERAGEIFS(RunningLog!$C$2:$C$1392,RunningLog!$A$2:$A$1392,">="&$E2,RunningLog!$A$2:$A$1392,"<="&EOMONTH($E2,12),RunningLog!$B$2:$B$1392,Option!G$1),"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    10-16-2014
    Location
    Vancouver, WA
    MS-Off Ver
    2010
    Posts
    29

    Re: How to calculate average by month and year

    WHOA!!! I LOVE EXCEL! How in the world do you remember this stuff Ford? Thank you.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to calculate average by month and year

    Just practice I guess thanks for the kind words and the rep

+ 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. [SOLVED] Average value for the given month irrespective of any year
    By thilag in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-04-2014, 05:51 AM
  2. [SOLVED] Average if data is in the same month of the same year
    By manofcheese in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-04-2014, 05:05 PM
  3. [SOLVED] I have the day and month but not the Year-need a macro to calculate the Year
    By iplayball in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-31-2013, 01:31 AM
  4. average for month, 1/4 year, year
    By miheec in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-10-2012, 06:34 PM
  5. to calculate this month, this year, last year figure
    By mingali in forum Excel General
    Replies: 7
    Last Post: 08-07-2010, 03:22 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