taking average

**rochenge** · 02-13-2016, 12:21 PM

Dear friends
I need help here; I have daily data on trading volume- which i hope to develop some measure. the first step is to take a monthly average and spread it through every trading day. for example take stock A-i would wish to obtain the average volume for month 1 and the spread that average on every day. the real issue is that the sheet has about 60000 lines and so doing a manual work would take alot of effort and time. kindly help me with how i can program this task.
Date volume Monthly_average
6-Jan-09 697 28762.36842
7-Jan-09 1835 28762.36842
8-Jan-09 500 28762.36842
9-Jan-09 114300 28762.36842
12-Jan-09 800 28762.36842
13-Jan-09 14100 28762.36842
14-Jan-09 3100 28762.36842
15-Jan-09 132034 28762.36842
16-Jan-09 33000 28762.36842
19-Jan-09 34000 28762.36842
20-Jan-09 49900 28762.36842
21-Jan-09 3500 28762.36842
22-Jan-09 3275 28762.36842
23-Jan-09 21513 28762.36842
26-Jan-09 112300 28762.36842
27-Jan-09 16431 28762.36842
28-Jan-09 2700 28762.36842
29-Jan-09 2000 28762.36842
30-Jan-09 500 28762.36842

**sourabhg98** · 02-13-2016, 12:46 PM

Hi rochenge,

It seems to be unclear for what you are trying to achieve.

It would be good if you could attach a sample workbook here and explain in more detail.
You can attach a sample workbook by clicking on "Go Advanced" button at the bottom of the post and then clicking on the "Paper Clip" icon from the tools above.

**rochenge** · 02-14-2016, 02:15 AM

Dear friends
I need help here; I have daily data on trading volume- which i hope to develop some measure.
the first step is to take a monthly average and spread it through every trading day.
for example take stock A; I would wish to obtain the average volume for month 1 and the spread that average on every day.
the real issue is that the sheet has about 60000 lines and so doing a manual work would take a lot of effort and time.
kindly help me with how i can program this task.I have attached a sample workbook indicating the kind of output am looking for;
specifically i need to fill column C and the lines are too many

**Shareez Saleem** · 02-14-2016, 03:13 AM

Refer the attached sheet.

**sourabhg98** · 02-14-2016, 04:33 AM

Hi,
You can go for AVERAGEIFS formula which calculates the average on the basis of various criteria specified.

Here is the formula-

Please Login or Register  to view this content.

Using the formula-

1. Paste the formula above in cell C2.
2. Copy the formula down till the end of your range.

Understanding the formula-

=AVERAGEIFS(B:B,A:A,">"&EOMONTH(A2,-1),A:A,"<"&EOMONTH(A2,0))

B:B >> Range for which the average should be calculated.

A:A >> Range for the first criteria

">"&EOMONTH(A2,-1) >> This is the First criteria. EOMONTH(A2,-1) returns the last date of the month before the date in cell A2, and the greater than symbol checks if the data in Criteria Range 1 is greater than EOMONTH(A2,-1)

A:A >> Range for the second criteria.

"<"&EOMONTH(A2,0) >>This is the second criteria. EOMONTH(A2,0) returns the last date of the month for the date in cell A2, and the smaller than symbol checks if the data in Criteria Range 1 is smaller than EOMONTH(A2,0).

Finally the formula calculates the average for all the values for which both conditions are fulfilled.

**rochenge** · 02-14-2016, 05:22 AM

Dear Sourabhg98,
I like your approach, one problem though is that the average computed is not correct,
what could be missing in the criteria or did i get the formula correct?
here is the outcome of the formula you posted.
for example for the first month the correct average should be 28762.36842 while the formula gives me 205834.8

**sourabhg98** · 02-14-2016, 05:29 AM

Are you sure?
In the file you attached it shows me 28762.37 in Cell C2 which is the correct average as you said.

However, I just missed one equal to sign,

Please Login or Register  to view this content.

But the previous formula is also returning the same result as you said.
Please check again.

excelforum-1.jpg

**rochenge** · 02-14-2016, 05:43 AM

what is puzzling Sourabhg98, is that- as i said earlier, the rows of the real data are more
than the sample i posted- since the forum refuses large attachement, so the formula works for
the sample but each time i add more rows or i copy the formula to the real extended data i get incorrect averages
although the pattern of breaking after each month works perfectly.
Rogers