+ Reply to Thread
Results 1 to 2 of 2

AVERAGEIF - calculate monthly average

  1. #1
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 2016
    Posts
    260

    AVERAGEIF - calculate monthly average

    In column A I have the date in dd/mm/yyyy format (e.g. 20/01/2000) with 365 rows (one for every day of the year) and in column B I have "widgets produced per day", one value for every day of the year.

    I am trying to work out what the monthly average widgets produced is but don't want to have to use the AVERAGE function and then manually select the range corresponding to each month of the year. I've been trying to use the AVERAGEIF function in order to group the days into months before taking the average but can't get the correct syntax, e.g.

    {=AVERAGEIF(B1:B366, MONTH(A1:A366)=1)}

    is supposed to only take the average if MONTH = 1 (January) but it doesn't work.

    I've also tried inserting a 'helper column' in column B that reports the current month, e.g. in B1: =MONTH(A1)

    and then the AVERAGEIF formula is:

    {=AVERAGEIF(C1:C366, B1:B366=1)}

    but this doesn't work either. Can anyone offer any help? The ideal solution would be to not need helper columns and to keep the whole formula within one cell.

    Regards
    Rob

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: AVERAGEIF - calculate monthly average

    =AVERAGE(IF(month(B1:B366)=1,C1:C366))

    Confirm COntrol+Shift+Enter
    The AVEARGEIF() function does not work on array so you can not use MONTH() unless you have got AVERAGEIFS() where you can specify the beginning and the end of the month.
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

+ 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: 2
    Last Post: 09-21-2013, 10:49 AM
  2. Calculate weighted average for multiple products on a monthly basis
    By arvadata in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-08-2013, 12:07 PM
  3. How to calculate monthly average
    By mikejones21 in forum Excel General
    Replies: 5
    Last Post: 07-22-2012, 11:51 PM
  4. [SOLVED] calculate monthly average percentage of change
    By vikgarden in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-15-2005, 03:06 PM
  5. Need formula to calculate average monthly percentage of change...
    By vikgarden in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2005, 02:06 PM

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