Averages based on dates

[Guest]

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

 

[Doug Glancy]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[daddylonglegs]

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+ENTE

 

[Guest]

Thank you both - two solutions!!