Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 56
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating an average of data between two dates lukestkd Excel Worksheet Functions 1 July 30th 12 09:59 PM
Calculating an average Maggie Excel Worksheet Functions 1 August 22nd 08 03:50 AM
Calculating number of days between two dates that fall between two other dates [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM
Working with Dates: Calculating A Running Total for Days Held Mcasteel[_48_] Excel Programming 1 November 18th 04 08:09 PM
Working with Dates: Calculating A Running Total for Days Held Mcasteel[_47_] Excel Programming 2 November 17th 04 11:57 PM


All times are GMT +1. The time now is 08:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"