AVEDEV

What it does

AVEDEV(number1, [number2], ...)

AVEDEV(number1, [number2], …) returns the mean absolute deviation about the mean of the admitted values. For a finite sample (x_1,\dots,x_n),

x̄     = (1/n) · Σ_{i=1..n} x_i
AVEDEV = (1/n) · Σ_{i=1..n} |x_i − x̄|

Both sums run over the same (n); the divisor is (n), not (n-1). There is no "population versus sample" pair for this statistic in Excel — AVEDEV is the only member.

Three properties are worth having in front of you, because they are the ones that get misremembered:

1. It is a deviation about the mean, not about the median. The functional (c \mapsto \sum_i |x_i - c|) is minimised at the median, not the mean. So AVEDEV is not the smallest achievable mean absolute deviation of the sample, and it is not the statistic usually written MAD in the robust-statistics literature (which is the median absolute deviation about the median). Excel has no function for that one.
2. It is not on the same scale as a standard deviation. For a sample drawn from a normal distribution with standard deviation (\sigma), the expected absolute deviation is (\sigma\sqrt{2/\pi} \approx 0.7979,\sigma). Comparing an AVEDEV against a STDEV.S on the same data and concluding the data is "less spread than expected" is a scale error, not a finding.
3. It is not differentiable in the data. (|x_i - x̄|) has a corner wherever (x_i = x̄). Two datasets that differ by an arbitrarily small perturbation can have AVEDEV values whose derivative with respect to that perturbation flips sign. This matters for anything that optimises through AVEDEV, and it matters for last-bit comparison: near a tie the branch taken by (|\cdot|) is decided by the low bits of (x_i - x̄).

Range: AVEDEV is non-negative, and zero exactly when every admitted value is equal. Units are the units of the data, unlike a variance.

Arguments

• A value written directly at the call site — AVEDEV(TRUE, "2") — is coerced. "Logical values and text representations of numbers that you type directly into the list of arguments are counted."
• The same value arriving through a reference or array is skipped, not coerced. "If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included."

So AVEDEV(A1:A2) and AVEDEV(A1, A2) are not interchangeable when the cells hold anything other than numbers. See Coercion and lifting for the general shape of this rule.

Microsoft also documents 1 to 255 arguments, and the caution that "AVEDEV is influenced by the unit of measurement in the input data" — which is the documentation's way of saying what the (\sqrt{2/\pi}) remark above says: this statistic carries the data's units and is not standardised.

Empty cells and omitted argument slots contribute nothing and do not enter (n); see Missing versus Empty.

Try it

Quirks

Result and edge cases

Returns Number.

• One admitted value. (x̄ = x_1), so the result is exactly 0. This is a real answer, not a degenerate one, and it is why a one-cell AVEDEV never errors.
• All values equal. Exactly 0, and exactly the IEEE zero — every deviation is a true zero before summation.
• Arrays. The surface lifts natively (lift_broadcast_profile: surface_native) — it is a reduction, so array arguments are consumed rather than broadcast over.

Errors

How to implement

Numerical notes

AVEDEV is a two-pass statistic and cannot be made one-pass: the mean must be known before any deviation can be formed, and unlike the variance there is no algebraic expansion ((\sum x^2 - n x̄^2)) that lets a single accumulator stand in, because (|\cdot|) does not expand. Every implementation therefore either stores the data or reads it twice. The reference engine stores it.

The interesting error analysis is the interaction of two stages:

1. The mean. A plain left-to-right accumulation of (\sum x_i) has a worst-case relative error bound growing like (n u) (with (u) the unit roundoff) and, in the standard analysis, a bound proportional to (\sum |x_i| / |\sum x_i|) — the condition number of summation. Data of mixed sign, or data with a large common offset, is where this bites. Higham's Accuracy and Stability of Numerical Algorithms chapter 4 is the standard treatment; pairwise summation reduces the growth to (\log_2 n), and compensated (Kahan/Neumaier) summation removes the (n) dependence almost entirely.
2. The deviations. (x_i - x̄) is a subtraction of nearby quantities. When the data has a large offset relative to its spread — say values around (10^9) that differ in the third decimal — the subtraction is exact in IEEE arithmetic (Sterbenz's lemma applies when the operands are within a factor of two), but the input (x̄) already carries the error of stage 1, and that error is inherited by every deviation.

There is a mitigating structure worth knowing. Write (g(c) = \sum_i |x_i - c|). Away from ties, (g'(c) = #{i : x_i < c} - #{i : x_i > c}). So an error (\delta) in the computed mean perturbs the deviation sum by roughly (\delta \cdot (\text{below} - \text{above})). On data that is roughly symmetric about its mean this factor is small, and AVEDEV is far less sensitive to the accuracy of (x̄) than one might fear. On strongly skewed data the factor approaches (\pm n), and the mean's error is amplified by the sample size. This is the analysis that tells you which datasets to build a probe suite from.

Excel's statistical lane has a documented history of exactly this class of weakness; Morten Welinder's work on Gnumeric's statistical functions, and the accompanying published critiques of Excel's statistical accuracy, are the standard entry point to that literature and are worth reading before assuming any spreadsheet's dispersion statistic is well conditioned.

Rust code

1 avedev "AVEDEV" (1, 255) [R "number1", R "number2"] |s, a, sv| {
    let values = all(s, a, sv, Held::Numbers)?;
    if values.is_empty() {
        return fail(WorksheetErrorCode::Num);
    }
    let mean = values.iter().sum::<f64>() / values.len() as f64;
    let avedev = values.iter().map(|v| (v - mean).abs()).sum::<f64>() / values.len() as f64;
    s.number(avedev)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• DEVSQ — the same construction with squares instead of absolute values: (\sum (x_i - x̄)^2), unnormalised. DEVSQ, VAR.P and AVEDEV are three normalisations of the same deviation scan.
• STDEV.S / STDEV.P — the squared-deviation dispersion measures. See the (\sqrt{2/\pi}) scale factor above before comparing them to AVEDEV numerically.
• MEDIAN — the centre that would actually minimise the absolute-deviation sum. There is no built-in that computes the median absolute deviation; it needs an array formula.
• Confused with: the robust MAD estimator. They share an abbreviation and almost nothing else.

Named variants

No named variants are recorded yet.