Cumulative average based on predefined calendar month [SOLVED]

[siow333]

Hi, I have a table as below. I would like to add another column to calculate average cumulative closing rate daily whenever a new closing rate is entered. The cumulative average needed to be reset whenever the month changed. User will manually key in date, month and closing rate.

[pre]

Date            Month      Closing Rate
25/2/2013    February-13      3.9964
26/2/2013      March-13       4.0593
27/2/2013      March-13       4.0498
28/2/2013      March-13       4.0529
1/3/2013       March-13       4.0530
2/3/2013       March-13       4.0321

[/pre]

 

[NARAYANK991]

Hi ,


Let us assume your data is in row 2 onwards , in the range A2:C7 for the data you have posted.


If we assume two named ranges Months referring to B2:B7 , and Rates referring to C2:C7 , then you can have the following formula in D2 :


=IF(C2="","",IF(B2<>B1,C2,AVERAGE(OFFSET(Rates,MATCH(B2,Months,0)-1,,COUNTIF($B$2:B2,B2)))))


Copy this downwards.


If you want , you can make the named ranges Months and Rates dynamic , by making them refer to :


Months referring to : =Sheet1!$B$2:INDEX(Sheet1!$B:$B,ROWS(Rates)+1)


Rates referring to : =Sheet1!$C$2:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$C:$C))


Narayan

 

[siow333]

Hi Narayan, it works perfectly. Thank you very much.