ExcelBanter - Get the AVG price for an entire month

Extropian,

I'm sure you will get multiple answers. If you have Excel 2007, look up the
help documentation on the AVERAGEIFS function. If you have Excel 2003, then
you can use an array formula or the SUMPRODUCT formula. I've include one
method below (assuming you don't have Excel 2007).

Data Assumption:
Dates: A1:A125
Values: B1:B125

Text Inputs (i.e. Labels):
D1: Start Date
D2: End Date
D3: Sum
D4: Count
D5: Average

Formulas:
E1: 1/1/2003
E2: =EOMONTH(E1,0)
E3: =SUMPRODUCT(($A$1:$A$125=E1)*($A$1:$A$125<=E2)*($ B$1:$B$125))
E4: =SUMPRODUCT(($A$1:$A$125=E1)*($A$1:$A$125<=E2)) [OR =End Date - Start
Date + 1 for every day in the month, regardless of whether it is in column A
or not]
E5: =E3/E4

F1: =DATE(YEAR(E1),MONTH(E1)+1,DAY(E1))
F2:F5: Fill Right
G1:G5: Fill Right

With the SUMPRODUCT function used in this way, be sure to size all the
arrays to be the same, otherwise the function will return an error. Also,
check the =, <= logic to ensure it is set up the way you desire. I hope
this helps.

Best,

Matthew Herbert

"Extropian" wrote:

Hello, I need a hand here please...

I have two worksheets, source and destination.

The source worksheet has a column of dates listed as 1Jan2003, 2Jan2003,
3Jan2003, (dmmmyyyy) etc. with corresponding prices in the next column.

I want to get the AVG of all Jan2003 (mmmyyyy) prices and place it in the
destination worksheet in a single row. One row for Feb2003, etc. etc.

What's the easiest way to do this? Thanks in advance for your help!

- Heather