On Thursday, October 1, 2015 at 2:42:57 AM UTC+5:30, MyVeryOwnSelf wrote:
I have a table consisting two columns - Date (dd/mm/yyyy) & Fuel Price Rate
01-03-2015 67.92
02-04-2015 67.48
15-04-2015 66.81
01-05-2015 70.44
16-05-2015 73.76
16-06-2015 74.42
01-07-2015 74.09
15-07-2015 71.57
01-08-2015 69.15
15-08-2015 68.1
01-09-2015 66.5

I would like to populate in another column, the average price rate for a specific duration, i.e between the 16th of a previous Month to 15th of the succeeding month, depending upon the date indicated in the first column.

A1 B1 C1
01-03-2015 67.92 = 67.92 ( As only 1 data between 16/2 and 15/3)
02-04-2015 67.48 = Average (67.91, 67.48, 66.81) ..data for period 16/3 to 15/4
15-04-2015 66.81 = Same as Above .. as date falls in the previous period
01-05-2015 70.44 = Average (66.81,70.44) .. data for period 16/4 to 15/5)
16-05-2015 73.76 = Average (70.44, 73.76).. data for period (16/5 to 15/6)
16-06-2015 74.42 = Average (74.42,74.09,71.57).. data for period (16/6 to 15/7)
01-07-2015 74.09 = Same as above... as date falls in the previous period
15-07-2015 71.57 = Same as above -- as date falls in the previous period
01-08-2015 69.15 = Average (71.57,69.15,68.1) ..data for period 16/7 to 15/8
15-08-2015 68.1 = Same as above .. as date falls in the previous period
01-09-2015 66.5 = Average (68.1, 66.5) ..data for period (16/8 to 15/9)


One way is to put this in C1 and copy down:
=(SUMIF(A:A,"<="&EOMONTH(A1,-1+(DAY(A1)15))+15,B:B)
-SUMIF(A:A,"<"&EOMONTH(A1,-2+(DAY(A1)15))+16,B:B)) /
(COUNTIF(A:A,"<="&EOMONTH(A1,-1+(DAY(A1)15))+15)
-COUNTIF(A:A,"<"&EOMONTH(A1,-2+(DAY(A1)15))+16))

(Hopefully, I'm on the right track -- some of the example lines in the original post are puzzling. For example, I understand why 16/7 to 15/8 is the date range for 01-08-2015, but then I would've thought the Average() would have only two terms instead of three.)

Here's an explanation of the C1 formula.

First, informally, the top of the date range for A1 is
TOP = EOMONTH(A1,-1+(DAY(A1)15))+15

Similarly, the bottom is
BOTTOM = EOMONTH(A1,-2+(DAY(A1)15))+16

Next, the average of some numbers is a "fraction"
SUM("numbers")/COUNT("numbers")

Since this problem involves filtering the rows, we will use SUMIF() and COUNTIF().

To compute the "numbers" for dates in the specified range, we can start with all the numbers with dates<=TOP, and then subtract off the numbers with dates<BOTTOM. This way, the numerator of the "fraction" is
SUMIF(A:A, <=TOP, B:B) - SUMIF(A:A, <BOTTOM, B:B)
and the denominator is
COUNTIF(A:A, <=TOP) - COUNTIF(A:A, <BOTTOM)

Folding these steps together yields the C1 formula above.

Hope this helps. Adapt the above as needed.

Thks for the help. You got that right. My mistake.. It should have two terms