How to Average Shipping Times in Excel Excluding Outliers

Inaccurate logistics forecasting often stems from outlier shipping delays-like customs bottlenecks-that heavily distort your average transit times. While standard carrier performance logs provide a necessary baseline of raw data, relying on basic averages misrepresents typical performance.

Implementing robust statistical formulas in Excel grants supply chain leaders the precise clarity needed for inventory planning. Crucially, a key stipulation of this methodology is establishing a consistent exclusion threshold (such as a 10% trim rate) to maintain data integrity. Utilizing the TRIMMEAN function serves as a proven industry solution to isolate core shipping windows.

Below, we will detail how to construct this formula step-by-step to optimize your logistics reporting.

In logistics and supply chain management, tracking average shipping times is one of the most critical Key Performance Indicators (KPIs). It helps businesses manage customer expectations, evaluate carrier performance, and optimize warehouse workflows. However, relying on a simple arithmetic mean can often lead to highly distorted insights.

Imagine 95% of your orders arrive within 2 to 4 days, but a small handful of shipments get stuck in customs or lost in transit, taking 45 days to resolve. If you calculate a basic average, these extreme delays (outliers) will artificially inflate your average shipping time, painting an inaccurate picture of your day-to-day operations. To get a realistic view of your standard transit performance, you need to exclude these statistical anomalies.

In this guide, we will explore three powerful ways to write an Excel formula that averages shipping times while excluding outliers: the straightforward TRIMMEAN function, the robust Standard Deviation (Z-score) method, and the highly reliable Interquartile Range (IQR) method.

Why Simple Averages Lie (And How Outliers Skew Data)

Before diving into the formulas, let's look at a quick example of how outliers manipulate data. Consider the following shipping times (in days) for ten customer orders:

3, 4, 3, 5, 2, 4, 3, 40, 3, 4

If you calculate the standard average using =AVERAGE(), the result is 7.1 days. However, looking at the dataset, nine out of ten orders arrived in 5 days or less. The single order that took 40 days has completely distorted the metric, making your shipping service look much slower than it actually is. By identifying and excluding that "40," the true operational average is actually 3.4 days.

Method 1: The Quick Way Using TRIMMEAN

If you need a fast and easy solution that doesn't require complex statistical setups, Excel's built-in TRIMMEAN function is your best option.

The TRIMMEAN function calculates the mean of a dataset after excluding a specified percentage of data points from both the top and bottom tails of your distribution.

The Formula Syntax

=TRIMMEAN(array, percent)

• array: The range of cells containing your shipping times (e.g., C2:C101).
• percent: The fractional proportion of data points to exclude. For example, if you input 0.1 (10%), Excel will exclude the top 5% and bottom 5% of your data points, averaging the middle 90%.

Step-by-Step Example

Suppose your shipping durations are located in cells C2 through C1000. To calculate the average shipping time while trimming the top and bottom 5% of anomalies (a total of 10% excluded), use the following formula:

=TRIMMEAN(C2:C1000, 0.1)

Pros: incredibly simple to implement and requires no helper columns.

Cons: It trims data symmetrically. If you only want to exclude extremely long shipping times (high outliers) but want to keep exceptionally fast times (low outliers), TRIMMEAN will still discard data from both ends.

Method 2: Standard Deviation Method (For Normally Distributed Data)

If your shipping times generally follow a normal bell-curve distribution, you can use standard deviation to target and remove values that fall too far from the average. Historically, values that fall more than 2 or 3 standard deviations away from the mean are mathematically classified as outliers.

We can construct a formula using AVERAGEIFS, AVERAGE, and STDEV.S to dynamically exclude anything beyond two standard deviations from the mean.

The Modern Excel (Office 365 / Excel 2021) Formula

By leveraging the LET function, we can write a clean, self-contained formula that calculates the mean, the standard deviation, and then filters the dataset:

=LET(
    shipping_times, C2:C1000,
    avg, AVERAGE(shipping_times),
    std, STDEV.S(shipping_times),
    AVERAGEIFS(shipping_times, shipping_times, ">=" & (avg - 2 * std), shipping_times, "<=" & (avg + 2 * std))
)

How It Works

1. shipping_times: We define our raw data range (C2:C1000).
2. avg & std: The formula calculates the baseline average and standard deviation.
3. AVERAGEIFS: It averages only the values that are greater than or equal to 2 standard deviations below the mean, and less than or equal to 2 standard deviations above the mean.

If you are using an older version of Excel that does not support the LET function, you can achieve the same result by calculating your limits in separate helper cells:

• Cell E2 (Baseline Average): =AVERAGE(C2:C1000)
• Cell E3 (Standard Deviation): =STDEV.S(C2:C1000)
• Final Formula: =AVERAGEIFS(C2:C1000, C2:C1000, ">="&(E2 - 2*E3), C2:C1000, "<="&(E2 + 2*E3))

Method 3: Interquartile Range (IQR) Method (Best for Real-World Shipping Data)

Real-world shipping times are rarely normally distributed; they are typically skewed to the right (meaning you have a hard floor at zero days, but an infinite ceiling for delays). In skewed datasets, the Standard Deviation method can fail because the outliers themselves skew the standard deviation calculation.

The Interquartile Range (IQR) method, also known as Tukey's Fences, is the industry standard for skewed data. It calculates boundaries based on percentiles:

• Q1 (25th Percentile): The value below which 25% of the data lies.
• Q3 (75th Percentile): The value below which 75% of the data lies.
• IQR: The distance between the first and third quartiles (Q3 - Q1).

An outlier is defined as any value that falls below Q1 - 1.5 * IQR or above Q3 + 1.5 * IQR.

The Office 365 Formula (Dynamic & Self-Contained)

With the LET function, we can calculate this entire statistical process inside a single cell:

=LET(
    data, C2:C1000,
    q1, QUARTILE.INC(data, 1),
    q3, QUARTILE.INC(data, 3),
    iqr, q3 - q1,
    lower_bound, q1 - (1.5 * iqr),
    upper_bound, q3 + (1.5 * iqr),
    AVERAGEIFS(data, data, ">=" & lower_bound, data, "<=" & upper_bound)
)

Breaking Down the Logic

This formula finds the 25th (q1) and 75th (q3) percentiles of your shipping times, calculates the difference (iqr), and establishes bounds. Because shipping times cannot be negative, the lower_bound check keeps your exceptionally fast times safe, while the upper_bound cleanly slices off shipping delays that represent supply chain failures rather than standard operations.

Which Method Should You Choose?

To help you decide which approach fits your logistics reporting flow best, consider this comparison:

Operational Best Practices: Don't Just Delete Outliers

While excluding outliers is essential for establishing realistic shipping estimates for your customer-facing marketing, remember that outliers are still real data. A shipping time of 50 days points to an actual operational failure-whether it was a carrier strike, custom delays, or inventory issues.

Best practices dictate that you should build a dashboard with two distinct metrics:

1. Standard Average Delivery Time: Calculated using the IQR formula. Use this to set expectations on your checkout page (e.g., "Our packages typically arrive in 3 days").
2. Outlier Rate and Extremes: Track the percentage of packages flagged as outliers and their average resolution times. This highlights structural risks within your logistics network that require management's attention.

By implementing these robust Excel formulas, you will preserve the accuracy of your baseline logistics KPIs while retaining the insight needed to fix outlier bottlenecks.