AVEDEV in Google Sheets: Measure Average Absolute Deviation

• Introduced: first service launch date is not established by the official history reviewed; no numbered desktop introduction is assigned.
• Availability: documented in the rolling Google Sheets service. Examples target ordinary web-spreadsheet cells; mobile clients and imported file modes are not separately certified.
• Checked: October 14, 2026; official AVEDEV reference. Documentation and independently calculated examples, without native Sheets execution.

=AVEDEV(value1, [value2, ...])

Supply a value or range, then optional additional values or ranges. Google’s English reference defines deviation from the dataset mean and says text values are ignored. The output has the same unit as the input measurements; it is not a squared-unit variance.

These controls use English names and comma separators. The examples distinguish ordinary text labels from numbers. The reference does not establish a complete Boolean, numeric-text, blank or error-cell conversion table.

Enter numeric 3, 7, 7 and 11 in A2:A5, with Measurement in A1. Put =AVERAGE(A2:A5) in D2; the expected mean is 7. In B2 enter and fill through B5:

=ABS(A2-$D$2)

Expected helper values are 4, 0, 0 and 4. Compare:

=AVERAGE(B2:B5)
=AVEDEV(A2:A5)

Both expected results are 2. Without ABS, the signed deviations −4, 0, 0 and 4 would sum to zero. Cancellation does not mean the observations are identical.

On a separate sheet, put 3, 7, 7, 11 and Pending in A2:A6, with Pending stored as text.

=AVEDEV(A2:A6)

Under Google’s documented text-ignore rule, the expected result remains 2. Replacing Pending with numeric 0 changes both the mean and the distances: the mean becomes 5.6, absolute distances become 2.6, 1.4, 1.4, 5.4 and 5.6, and AVEDEV becomes 3.28.

A missing measurement and a measured zero are different data policies. Do not replace every unfinished label with zero merely to make a numeric column complete.

With the original four values in A2:A5 and numeric 15 in C2:

=AVEDEV(A2:A5,C2)

Expected result: 3.52. The combined mean is 8.6, and the absolute distances sum to 17.6 across five observations. The function does not first calculate separate subgroup deviations and average those results.

Use one combined population when that is the intended question. Groups with different centers can yield a different overall spread even when each subgroup is internally tight.

Enter 13, 17, 17 and 21 in E2:E5, adding 10 to every original measurement. =AVEDEV(E2:E5) still has expected result 2 because distances from the shifted mean are unchanged.

Enter −6, −14, −14 and −22 in F2:F5, multiplying each original value by −2. The expected AVEDEV is 4: reversing signs does not create negative absolute distances, while doubling the magnitude doubles their size.

These are exact mathematical invariances for the stated inputs, not a guarantee of floating-point behavior for extremely large or nearly equal numbers.

For a separate dataset 3, 7, 7 and 31, the mean is 12 and AVEDEV has expected result 9.5, from distances 9, 5, 5 and 19.

The median is 7. Absolute distances from that median are 4, 0, 0 and 24; their median is 2. That median-based statistic is different from AVEDEV. Specify both the center and the way distances are summarized rather than using the ambiguous abbreviation MAD without a definition.

MEDIAN and DEVSQ answer different spread or center questions. Squaring deviations, as DEVSQ does, also changes the influence of a large departure.

The worked examples contain at least two numeric observations. The current English reference does not pin singleton, empty-dataset or exact native error outputs; no such behavior is inferred from Excel or translated help variants. Literal numeric text and Boolean participation remain separate unverified controls.

Keep the original source values, selected range and measurement units beside any reported spread. A plausible small result can still describe the wrong subset or a column whose text measurements were skipped.

• Google AVEDEV help.
• Editorial testing standards.

Inputs are fictional. Approximate answers are independent mathematical expectations rounded to nine places, not captured native results. Complete type/error matrices, extreme-magnitude accuracy and cross-client behavior remain untested.