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

[Vlip]

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

 

[Barrie Davidson]

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?

 

[Aladin Akyurek]

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.

 

[Dave Hawley]

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