Need Coding help
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Need Coding help

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Guest

    Default

    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

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Guest

    Default

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

    -m

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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



User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com