# Excel 2007 : Calculate On Time Delivery

1. ## 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. ## Re: Calculate On Time Delivery

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

maybe

3. ## 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. ## 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"))

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

#### 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