Thread: Average number of days

Greetings!
I need help with what might be a programming issue, could possible just be a bunch of functions...

I would like to find the average number of days events take from start to finish.

My data is in 3 columns:

EventName StartDate EndDate

I have a ton of EventNames, each of which have a StartDate. But I want to calculate the average days to complete only for those EventNames that have EndDates.

So, if a cell in the column EndDate is greater than zero, then take that cell, subtract it from the corresponding cell in the StartDate column; Add all of them together and divide by the count of those rows in the EndDate column that have a value.

How do I put this in excel terms?

Example Spreadsheet:

EventName StartDate EndDate
E1 1/1/09 6/3/09
E2 5/6/09
E3 5/4/09 8/2/09
E4 2/5/09
E5 6/8/09
E6 8/7/09
E7 3/9/09 6/4/09

Does this question make sense?

You can do this in realtime right on the worksheet, no macro needed to "figure it out".

Add a column D called "Length" in D1, then this formula in D2, copied down:
=IF(C2, C2-B2, "")

Then anywhere you want, this formula will give you the current average at all times:
=AVERAGE(D:D)

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)