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: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

=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?

## Like this thread? Share it with others