+ Reply to Thread
Results 1 to 8 of 8

Formula help: Calculating Averages for Rows

  1. #1
    Registered User
    Join Date
    05-11-2011
    Location
    Chester, Va
    MS-Off Ver
    Excel 2007
    Posts
    6

    Formula help: Calculating Averages for Rows

    Hello. Brand new to excel here so please excuse if this is a dumb and obvious question. I am trying to come up with a formula that will calculate 2 dates in different rows. For my business, need to come up with something that shows our on time percentages of completed tasks. The Column/row looks something like this:

    Due date Completed date

    05-11-11 05-11-11
    05-12-11 05-14-11
    05-10-11 05-10-11
    07-26-11 05-31-11
    06-01-11 06-15-11

    and so on and so forth. I need to make something that calculates the on time percentage. Can anyone help with this please? Thanks for any and all comments.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Formula help: Calculating Averages for Rows

    Are you trying to find the average difference in dates?

    If so, try:

    =SUMPRODUCT(--(A1:A5-B1:B5))/COUNT(A1:A5)

    where A1:B5 contain the dates.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    05-11-2011
    Location
    Chester, Va
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Formula help: Calculating Averages for Rows

    Thanks. I would be trying to find the average "on time" percentage.

    For my above example, I was on time for 3 of 5, 60%. On time meaning my completed date matches or is before my due date.

    So I guess a formula would have to be for how many times my completed date matched or was before my due date. Does that make sense?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Formula help: Calculating Averages for Rows

    Try then:

    =SUMPRODUCT(--(B2:B6<=A2:A6))/COUNT(A2:A6)

  5. #5
    Registered User
    Join Date
    05-11-2011
    Location
    Chester, Va
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Formula help: Calculating Averages for Rows

    I put it into a row to calculate and it gave me #DIV/0!

    =SUMPRODUCT(--(D57:D66<=E57:E66))/COUNT(E57:E66) is what I used. Still trying to get it to work. Thanks for the formula.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Formula help: Calculating Averages for Rows

    Does this range not have actual dates: E57:E66 ?

  7. #7
    Registered User
    Join Date
    05-11-2011
    Location
    Chester, Va
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Formula help: Calculating Averages for Rows

    Yes, it has actual dates. The dating convention I'm using is 05-12-11. I tried changing the data to both short date and long date. still gives me that same message.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Formula help: Calculating Averages for Rows

    See attached.

    Note: I added another condition in case you have blanks in column D.... but it did not error out for me even without that add condition.

    Check that the columns are actually formatted as Date... If you change format to General, you should see a 5 digit serial number in place of dates....
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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