Averages based on dates
 

A bit of help with a formula will be appreciated.
In column A I have start dates for a series of events and in column B are
the corresponding finish dates. Currently, 72 rows are in use but this list
will continue to grow, 4 - 8 per month. The dates are in the mm/dd/yy
format.
In column C calculated the number of elapsed weeks, =(b2-a2)/7. Formula is
copied down to the last row.
I then calculate a running average for elapsed weeks with =AVERAGE(C:C)/7

What I can not figure out is how to calculate the average elapsed weeks, for
last calendar month ( last month is based upon completion date), and to get
that to advance a month on the first of the next month. Therefore on Feb
1the average elapsed weeks should be calculated for Jan, and on Mar 1, the
calculation changes for elapsed weeks in Feb, etc.

Help will be greatly appreciated.
Paul

Averages based on dates
 

Paul,

I bet there's a shorter way to do this, but it seems to work:

=SUMPRODUCT((MONTH(B2:B1000)=MONTH(NOW())-1)*(C2:C1000))/SUMPRODUCT((MONTH(B2:B1000)=MONTH(NOW())-1)*1)

hth,

Doug


"PA" wrote in message
...
A bit of help with a formula will be appreciated.
In column A I have start dates for a series of events and in column B are
the corresponding finish dates. Currently, 72 rows are in use but this
list
will continue to grow, 4 - 8 per month. The dates are in the mm/dd/yy
format.
In column C calculated the number of elapsed weeks, =(b2-a2)/7. Formula
is
copied down to the last row.
I then calculate a running average for elapsed weeks with
=AVERAGE(C:C)/7

What I can not figure out is how to calculate the average elapsed weeks,
for
last calendar month ( last month is based upon completion date), and to
get
that to advance a month on the first of the next month. Therefore on Feb
1the average elapsed weeks should be calculated for Jan, and on Mar 1, the
calculation changes for elapsed weeks in Feb, etc.

Help will be greatly appreciated.
Paul

Averages based on dates
 

Thanks for the feedback, but that didnt quite work.

If I removed all the March, 06 dates, the answer should have been zero since
March is the previous month - that didnt happen
Also the calculation was off, lower, than if I just calculated the March
average using the Average function manually.
I tried both Enter and Ctrl+Shift+Enter (just in case) and got no difference.


"Doug Glancy" wrote:

Paul,

I bet there's a shorter way to do this, but it seems to work:

=SUMPRODUCT((MONTH(B2:B1000)=MONTH(NOW())-1)*(C2:C1000))/SUMPRODUCT((MONTH(B2:B1000)=MONTH(NOW())-1)*1)

hth,

Doug


"PA" wrote in message
...
A bit of help with a formula will be appreciated.
In column A I have start dates for a series of events and in column B are
the corresponding finish dates. Currently, 72 rows are in use but this
list
will continue to grow, 4 - 8 per month. The dates are in the mm/dd/yy
format.
In column C calculated the number of elapsed weeks, =(b2-a2)/7. Formula
is
copied down to the last row.
I then calculate a running average for elapsed weeks with
=AVERAGE(C:C)/7

What I can not figure out is how to calculate the average elapsed weeks,
for
last calendar month ( last month is based upon completion date), and to
get
that to advance a month on the first of the next month. Therefore on Feb
1the average elapsed weeks should be calculated for Jan, and on Mar 1, the
calculation changes for elapsed weeks in Feb, etc.

Help will be greatly appreciated.
Paul

Averages based on dates
 

Paul,

I forgot about years. Try this:

=SUMPRODUCT((YEAR(B2:B1000)=YEAR(NOW()))*(MONTH(B2 :B1000)=MONTH(NOW())-1)*(C2:C1000))/SUMPRODUCT((YEAR(B2:B1000)=YEAR(NOW()))*(MONTH(B2: B1000)=MONTH(NOW())-1))

I did notice in your original post that you refer to dividing by 7 in both
Column C cells and in your overall average of Column C, but I assume that's
not really the case.

Sumproduct does not require that you enter it as an array formula (just that
my formula works!).

hth,

Doug

"PA" wrote in message
...
Thanks for the feedback, but that didnt quite work.

If I removed all the March, 06 dates, the answer should have been zero
since
March is the previous month - that didnt happen
Also the calculation was off, lower, than if I just calculated the March
average using the Average function manually.
I tried both Enter and Ctrl+Shift+Enter (just in case) and got no
difference.


"Doug Glancy" wrote:

Paul,

I bet there's a shorter way to do this, but it seems to work:

=SUMPRODUCT((MONTH(B2:B1000)=MONTH(NOW())-1)*(C2:C1000))/SUMPRODUCT((MONTH(B2:B1000)=MONTH(NOW())-1)*1)

hth,

Doug


"PA" wrote in message
...
A bit of help with a formula will be appreciated.
In column A I have start dates for a series of events and in column B
are
the corresponding finish dates. Currently, 72 rows are in use but this
list
will continue to grow, 4 - 8 per month. The dates are in the mm/dd/yy
format.
In column C calculated the number of elapsed weeks, =(b2-a2)/7.
Formula
is
copied down to the last row.
I then calculate a running average for elapsed weeks with
=AVERAGE(C:C)/7

What I can not figure out is how to calculate the average elapsed
weeks,
for
last calendar month ( last month is based upon completion date), and to
get
that to advance a month on the first of the next month. Therefore on
Feb
1the average elapsed weeks should be calculated for Jan, and on Mar 1,
the
calculation changes for elapsed weeks in Feb, etc.

Help will be greatly appreciated.
Paul

Averages based on dates
 

That is fantastic.
Thank you very much. I greatly appreciate your effort!!

"Doug Glancy" wrote:

Paul,

I forgot about years. Try this:

=SUMPRODUCT((YEAR(B2:B1000)=YEAR(NOW()))*(MONTH(B2 :B1000)=MONTH(NOW())-1)*(C2:C1000))/SUMPRODUCT((YEAR(B2:B1000)=YEAR(NOW()))*(MONTH(B2: B1000)=MONTH(NOW())-1))

I did notice in your original post that you refer to dividing by 7 in both
Column C cells and in your overall average of Column C, but I assume that's
not really the case.

Sumproduct does not require that you enter it as an array formula (just that
my formula works!).

hth,

Doug

"PA" wrote in message
...
Thanks for the feedback, but that didnt quite work.

If I removed all the March, 06 dates, the answer should have been zero
since
March is the previous month - that didnt happen
Also the calculation was off, lower, than if I just calculated the March
average using the Average function manually.
I tried both Enter and Ctrl+Shift+Enter (just in case) and got no
difference.


"Doug Glancy" wrote:

Paul,

I bet there's a shorter way to do this, but it seems to work:

=SUMPRODUCT((MONTH(B2:B1000)=MONTH(NOW())-1)*(C2:C1000))/SUMPRODUCT((MONTH(B2:B1000)=MONTH(NOW())-1)*1)

hth,

Doug


"PA" wrote in message
...
A bit of help with a formula will be appreciated.
In column A I have start dates for a series of events and in column B
are
the corresponding finish dates. Currently, 72 rows are in use but this
list
will continue to grow, 4 - 8 per month. The dates are in the mm/dd/yy
format.
In column C calculated the number of elapsed weeks, =(b2-a2)/7.
Formula
is
copied down to the last row.
I then calculate a running average for elapsed weeks with
=AVERAGE(C:C)/7

What I can not figure out is how to calculate the average elapsed
weeks,
for
last calendar month ( last month is based upon completion date), and to
get
that to advance a month on the first of the next month. Therefore on
Feb
1the average elapsed weeks should be calculated for Jan, and on Mar 1,
the
calculation changes for elapsed weeks in Feb, etc.

Help will be greatly appreciated.
Paul

Averages based on dates
 

You could try something like this

=AVERAGE(IF(TEXT(B2:B100,"m-y")=TEXT(NOW()-DAY(NOW()),"m-y"),C2:C100))

confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=530768

Averages based on dates
 

Thank you both - two solutions!!

"daddylonglegs" wrote:


You could try something like this

=AVERAGE(IF(TEXT(B2:B100,"m-y")=TEXT(NOW()-DAY(NOW()),"m-y"),C2:C100))

confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=530768