Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating a running Average between dates
Hi, 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) I hope that I have been able to clearly spell out my requirement Thanks a lot for the help San |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating a running Average between dates
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating a running Average between dates
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating an average of data between two dates | Excel Worksheet Functions | |||
Calculating an average | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Working with Dates: Calculating A Running Total for Days Held | Excel Programming | |||
Working with Dates: Calculating A Running Total for Days Held | Excel Programming |