+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Calculate On Time Delivery

  1. #1
    Registered User
    Join Date
    03-10-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    1

    Question Calculate On Time Delivery

    Hi there

    I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:

    Column M - Estimated Delivery dates
    Column N - Actual Delivery dates
    Column O - =IF(SUM(M2-N2)>0,1,0)

    Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))

    This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?

    Any help much appreciated

    Thanks,

    James

  2. #2
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Calculate On Time Delivery

    =Sumproduct(--(N2:N252<>""),O2:O252)/COUNTA(N2:N252)

    maybe
    Windows 7 using Office 2007 & 2010

    Remember your [ code ] [ /code ] tags, makes reading soooo much easier

  3. #3
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    Re: Calculate On Time Delivery

    I'd be inclined to alter Column O calculation in the first instance

    Please Login or Register  to view this content.
    Then OnTime would simply be:

    Please Login or Register  to view this content.
    In your existing approach it seems same day delivery is not treated as OnTime - if it should be change M2>N2 to M2>=N2

  4. #4
    Registered User
    Join Date
    03-14-2011
    Location
    Washington state
    MS-Off Ver
    Excel Mac 2011
    Posts
    1

    Re: Calculate On Time Delivery

    I would differentiate between the state of shipments in column N by using a default label like "pending" rather than a blank prior to entering a delivery date. Then the formula in column O could distinguish between completed and pending orders like this:

    =IF(N1="pending","in transit",IF(N1<M1,"on time","late"))

    To calculate the average rate of on time deliveries you could then refer only to the cells that have "on time" or "late" in them -- ignoring the "in transit" cells.

    =COUNTIF(O1:O252,"on time")/(COUNTIF(O1:O252,"on time")+COUNTIF(O1:O252,"late"))

+ 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