Solved: Various formulae Questions

[Sir Babydum GBE]

Hi

On a new spreadsheet, designed to save Patagonia from complete moral breakdown*, I have on Column B from row 11, on all the odd numbered rows, the label "Started". From row 12, on all the even numbered rows, I have the label "Finished". The user will input a start date when a project is started, and an end date when it is finished. When the project is started again he will put a new start date below the last "Finished" entry and so forth.

What I'm struggling with is a few stats formulae.

How would I...

1. Get a formula to look down column C and calculate an average of how long each completed project took to finish. (e.g. C11 has "01 Jan 10" and C12 has "02 Jan 10" (2 days); C13 has "01 Feb 10" and C14 has "04 Feb 10" (4 days); C15 contains "01 Mar 10" and C16 contains "03 Mar 10" 93 days) - so the formula should return an average of 3 days.

2. Get a formula to calculate the average length of time between projects. So using the above example the time between the completion of the first project and the second is 30 days; between the second and third project is 25 days - so the result should be 27.5 days.

Many thanks in advance - Patagonia will be saved*.

As this is a formula question and not a vba question I'm going to cross post on Mr Excel, here: http://www.mrexcel.com/forum/showthread.php?p=2248797#post2248797
Hope you don't mind.


*That part is a lie

[bjacobowski]

Is there a reason everything needs to happen in one column? If it was split up so that start date was in column C and end date is in D, you could easily create hidden columns that calculate the length of each project, time between projects, etc. From there the averages are a relatively straight forward proposition.

[Sir Babydum GBE]

Is there a reason everything needs to happen in one column?I'm afraid so - or at least changing the spreadsheet format will create much more of a headache for me than (I hope) finding a formula solution.

[Bob Phillips]

1. An array formula

=AVERAGE((MOD(ROW(C12:C40),2)=0)*(C12:C40<>"")*((C12:C40)-(C11:C39)+1))

2. ANother array formula

=AVERAGE(IF(((C14:C40)-(C12:C38)>0)*(MOD(ROW(C14:C40),2)=0)*(C14:C40<>0),(C14:C40)-(C12:C38)))

[mdmackillop]

=(SUMPRODUCT(--(B11:B18="Finished"),C11:C18)-SUMPRODUCT(--(B11:B18="Started"),C11:C18)+COUNTIF(B11:B18,"Started"))/COUNTIF(B11:B18,"Started")

and dropping the first and last entry.

=(SUMPRODUCT(--(B12:B17="Started"),C12:C17)-SUMPRODUCT(--(B12:B17="Finished"),C12:C17))/COUNTIF(B12:B17,"Finished")

[Sir Babydum GBE]

Thanks both

Bob, I get an N/A error on your first formula and a DIV/0 on the second?

MDMACKILLOP, The first works fine except when a start date has been entered but not a completed date (on the last entry). Any way around this?

[p45cal]

Array-entered:
=AVERAGE(IF($B$12:$B$16="Finished",$C$12:$C$16-$C$11:$C$15))
and
=AVERAGE(IF($B$12:$B$16="Started",$C$12:$C$16-$C$11:$C$15))

Is there an inconsistency with differences? youi say:"C11 has "01 Jan 10" and C12 has "02 Jan 10" (2 days)" which I might count as 1 day, at the same time you say "the time between the completion of the first project and the second is 30 days" which I agree with. The net result is that when you add up all the gaps and project durations between two dates you'll have more days than there are between those two dates!

[mdmackillop]

Get the User to fix the data!

=IF(MOD(COUNT(C11:C18),2)=1,"Please match Start and End dates",(SUMPRODUCT(--(B11:B18="Finished"),C11:C18)-SUMPRODUCT(--(B11:B18="Started"),C11:C18)+COUNTIF(B11:B18,"Started"))/COUNTIF(B11:B18,"Started"))

[bjacobowski]

Here is a version that ignores a hanging start w/ no finish date. What does the double negative actually do? When evaluating the formula it doesn't really show what's going on w/ that step, the sumproduct just equals zero if you don't use the --.

=IF(MOD(COUNT($C$11:$C$18),2)=1,(SUMPRODUCT(--($B$11:$B$18="Finished"),$C$11:$C$18)-SUMPRODUCT(--($B$11:$B$18="Started"),$C$11:$C$18)+OFFSET($C$11,COUNT($C$11:$C$18)-1,0)+((COUNT($C$11:$C$18)-1)/2))/(((COUNT($C$11:$C$18)-1)/2)),(SUMPRODUCT(--($B$11:$B$18="Finished"),$C$11:$C$18)-SUMPRODUCT(--($B$11:$B$18="Started"),$C$11:$C$18)+(COUNT($C$11:$C$18)/2))/(COUNT($C$11:$C$18)/2))

[Aussiebear]

Hmmmm.....that last formula looks very much like a launch code for something at NASA.

[Bob Phillips]

What does the double negative actually do? When evaluating the formula it doesn't really show what's going on w/ that step, the sumproduct just equals zero if you don't use the --.

See http://xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation

[Bob Phillips]

You can modify Pascal's to cater for blanks

=AVERAGE(IF(($C$12:$C$16<>"")*($B$12:$B$16="Finished"),$C$12:$C$16-$C$11:$C$15+1))

[bjacobowski]

Hmmmm.....that last formula looks very much like a launch code for something at NASA.

Agreed...having seen the far more elegant solutions, one would hope my formula's output was worthy of something at NASA

XLD: Thanks, that article is really helpful

[Sir Babydum GBE]

Thanks all for your help. In the end...

{=AVERAGE(IF(B12:B26="Finished",IF(C12:C26<>"",C12:C26-C11:C25+1)))}

...seems to do the trick.

bjacobowski, that is one MONSTER formula! :bug:

mdmackillop, I couldn't get the user to fix the data because the users are required to put in start dates when they start, and end dates when the finish - so that the superviser can see what projects are ongoing. But thanks anyway

xld, as always... thanks. Your help is always valued.