Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Calculate Avg of Values in a column based on dates in anothe

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Forest, VA
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I have a column of sequential dates spanning several years in Column A. In column B, I have a set of values. I want to write a formula that will compute the average of values in column B by month and year. In other words, if my dates start on Jan 01, 2001 to present, I want a formula in column C lets say that will return the Average of values in Column B for Jan 1 to Jan 31, then Feb 1 to Feb 28, etc. Each subsequent row in Column C should contain the average of values in Column B for the next month. I wouldn't mind putting the formulas in Column D and creating entries in column C that would contain the month and year to summarize. Is there a way to do this with a formula? I know how I would do this in VBA but I want to try to do it with a formula. Can anyone suggest a formula to do this?

    Thanks for your help!
    Vlip

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-11 18:15, Vlip wrote:
    Hi,

    I have a column of sequential dates spanning several years in Column A. In column B, I have a set of values. I want to write a formula that will compute the average of values in column B by month and year. In other words, if my dates start on Jan 01, 2001 to present, I want a formula in column C lets say that will return the Average of values in Column B for Jan 1 to Jan 31, then Feb 1 to Feb 28, etc. Each subsequent row in Column C should contain the average of values in Column B for the next month. I wouldn't mind putting the formulas in Column D and creating entries in column C that would contain the month and year to summarize. Is there a way to do this with a formula? I know how I would do this in VBA but I want to try to do it with a formula. Can anyone suggest a formula to do this?

    Thanks for your help!
    Vlip
    Are you looking to have the average in column C in rows 1 to 12? If yes, you could use an array formula, something like:

    =SUM((MONTH($A$2:$A$60)=ROW())*($B$2:$B$60))/SUM((MONTH($A$2:$A$60)=ROW())*1)

    Change the references:

    $A$2:$A$60, to your range of dates in column A
    $B$2:$B$60, to your range of data to average

    Enter this formula in C1 and then press CTRL+SHIFT+ENTER (instead of ENTER). Then, copy down to C12.

    Is this what you are looking for?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-03-11 18:15, Vlip wrote:
    Hi,

    I have a column of sequential dates spanning several years in Column A. In column B, I have a set of values. I want to write a formula that will compute the average of values in column B by month and year. In other words, if my dates start on Jan 01, 2001 to present, I want a formula in column C lets say that will return the Average of values in Column B for Jan 1 to Jan 31, then Feb 1 to Feb 28, etc. Each subsequent row in Column C should contain the average of values in Column B for the next month. I wouldn't mind putting the formulas in Column D and creating entries in column C that would contain the month and year to summarize. Is there a way to do this with a formula? I know how I would do this in VBA but I want to try to do it with a formula. Can anyone suggest a formula to do this?

    Thanks for your help!
    Vlip
    Another formula-based approach

    Lets say that A2:B50 houses your data.

    Create a list of 3-letter month names in column C from C2 on (that is, "Jan", "Feb", etc.

    Create a list of years in row 1 from D1 on, e.g., 2001, 2002, etc. depending on your data.

    In D2 enter, copy across then down:

    =SUMPRODUCT((YEAR($A$2:$A$50)=D$1)*(TEXT($A$2:$A$50,"mmm")=$C2),$B$2:$B$50)/MAX(1,SUMPRODUCT((YEAR($A$2:$A$50)=D$1)*(TEXT($A$2:$A$50,"mmm")=$C2)))

    If your data area is changing frequently e.g., by new additions, you can switch to a formula that computes the data ranges dynamically:

    In C1 enter:

    =MATCH(9.99999999999999E+307,A:A)

    In D2 enter and copy across then down:

    =SUMPRODUCT((YEAR(OFFSET($A$2,0,0,$C$1,1))=D$1)*(TEXT(OFFSET($A$2,0,0,$C$1,1),"mmm")=$C2)*(OFFSET($B$2,0,0,$C$1,1)))/MAX(1,SUMPRODUCT((YEAR(OFFSET($A$2,0,0,$C$1,1))=D$1)*(TEXT(OFFSET($A$2,0,0,$C$1,1),"mmm")=$C2)))

    Note. You can also use the month numbers instead of 3-letter codes, which would requires using MONTH instead of TEXT.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Vlip

    Sounds like a Pivot Table with dates grouped by month would be ideal for this. This would honestly be the most efficient approach. If you do go with a Pivot Table I would also advise using a Dynamic range as the data range for the Pivot Table. I have quite a few examples here:


    http://www.ozgrid.com/Excel/DynamicRanges.htm

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •