Thanks:  0
Likes:  0

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

1. 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?

Vlip

2. 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?

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?

3. 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?

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

## User Tag List

#### Posting Permissions

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