+ Reply to Thread
Results 1 to 5 of 5

How to calculate average total working days for tasks completion

  1. #1
    Registered User
    Join Date
    01-29-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question How to calculate average total working days for tasks completion

    Hi,

    I'm newbie, and I hope you guys can help me to solve this problem
    I have a list of working files that contains the opening file dates, closing file dates, and also the status of working files. I have already calculate the working days of each of the working file using NETWORKDAYS function

    =IF(OR(B2="",C2=""),"",IF(ISERROR(NETWORKDAYS(B2,C2)),"N/A",NETWORKDAYS(B2,C2,$I$2:$I$12)-1 & " days"))

    and now I want to calculate the average of total working days excluding the blank cells and the error values but can't seem to find out how. I've tried this formula but it doesn't work either :

    =AVERAGE(IF(ISNUMBER(E2:E11),E2:E11))

    Please kindly help me to work this out
    I've also attached the Example file to help you understand my questions better.
    Thx before.
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to calculate average total working days for tasks completion

    Hi Laras,

    use below formula for days, see attached:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    EXAMPLE.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: How to calculate average total working days for tasks completion

    In E2
    =IF(OR(B2="",C2=""),"",IFERROR(NETWORKDAYS(B2,C2,$I$2:$I$12)-1,"N/A"))
    cell format #" days"


    Copy down

    In E12
    =AVERAGE(E2:E11)
    cell format #" days"
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    01-29-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: How to calculate average total working days for tasks completion

    Awesome!!

    Thanks that works great !!
    So I guess I must take out the "days" format in the NETWORKDAYS function so I can get the total average of the working days.

    Thank you for the useful advice

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to calculate average total working days for tasks completion

    Yes... including "days" is making them as text and you can not take the average of text


    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1