Need Coding help

[NotSoC#]

I hope can explain this well enough for you to understand.
In colum A i have Start Date
In Colum B i have Finish Date
In colum C i have Total Project Days

This is easy to do...........

I now need some code or something to search colum b to find how many projects were closed in Jan, Feb, Mar, etc. and output the result in Colum D.

For example if i have 3 projects finished in Jan i now need to find the average time of these it took these 3 to finish.(colum E)

So i need a table showing:
Month: CLosed Out: Avg Time(days)
Jan.........3.........160
Feb.........4.........190
Mar.........4.........300

Hope this makes semse to someone

Regards..............................C

 

[Guest]

The most logical way is to use an array formula, though if you've not used these before, they can be tricky. An easier way is to enter the months in column D
(eg in D2 enter Jan-2002, in D3 enter Feb-2002 etc)
In Column E you can then enter the average formula. Cell D2 should look something like:

=IF(COUNTIF($B$2:$B$10,TEXT(D2,"mmm-yyyy"))=0,0,SUMIF($B$2:$B$10,TEXT(D2,"mmm-yyyy"),$C$2:$C$10)/COUNTIF($B$2:$B$10,TEXT(D2,"mmm-yyyy")))

-mario

 

[Mudface]

Or you could use a pivot table on a separate sheet (maybe) and group your dates into months.

 

[Guest]

Sorry, that last part should have read cell E2 will look like.

-m

 

[Dave Hawley]

Sounds like an ideal job for the Database functions in Excel (Dfunctions). These are designed for this type of work and wont cause calculation slow down as arrays will. The Excel help has some good examples on these functions, just type: "database functions" in the help.

I have some examples also that you can download here: http://www.ozgrid.com/download/default.htm under DFunctionsWithValidation.zip