AVEDEV in WPS Spreadsheet: Average Absolute Deviation with Worked Checks

• Introduced: documented by 2019-05-30 in the official WPS lesson; original introduction is not established.
• Availability: WPS Office for Windows Personal; no numeric release is stated in the edition metadata.
• Scope: exact current supported builds remain unverified. The Windows lesson does not establish equivalent availability in other WPS clients.
• Checked: October 14, 2026 against function-specific documentation. Examples were independently calculated; no native WPS test was performed.

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

Supply values, names, arrays or references. The dated Windows Personal lesson describes 1–30 arguments; a range can hold multiple observations inside one argument. This historical argument limit is not certified for every current WPS build.

Referenced text, logical values and genuinely empty cells are ignored, while numeric zero is included. The lesson separately says directly supplied logical values and numeric text participate. Do not assume a referenced text cell is treated the same as a direct quoted argument.

Examples use English names and comma separators. Square brackets indicate optional arguments and are not typed. Keep source values numeric and place outputs outside the source data.

Enter 2, 4, 6 and 12 in A2:A5. In D2 calculate the mean with =AVERAGE(A2:A5), giving 6. In B2 enter =ABS(A2-$D$2) and fill down:

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

Both have expected result 3. The absolute distances total 12 across four observations. The helper column makes the center and denominator explicit, which helps detect a mistakenly selected range.

If you average the signed differences without ABS, they sum to zero. That cancellation does not mean there is no variation: positive and negative departures balance around the arithmetic mean.

On a separate sheet, enter 2, 4, 6, 12 and 0 in A2:A6:

=AVEDEV(A2:A6)

Expected result: 3.36. The new mean is 4.8; absolute distances are 2.8, 0.8, 1.2, 7.2 and 4.8, totalling 16.8. Dividing by five gives 3.36.

The zero is a measurement in this model. Replacing it with a genuinely empty cell restores the original four-observation result 3. A missing observation and an observed zero are different statistical inputs.

For the original four observations, create =A2+100 in E2 and =A2*10 in F2, filling through row 5:

=AVEDEV(E2:E5)
=AVEDEV(F2:F5)

Expected results are 3 and 30. Adding the same constant moves both the values and their mean without changing their distances. Multiplying by ten multiplies each distance by ten.

The mathematical scale rule uses the absolute magnitude of the multiplier: values multiplied by -2 have AVEDEV 6. AVEDEV carries the same measurement units as its inputs, so a value in seconds cannot be compared directly with a value in minutes without conversion.

Enter 2, 3, 4 and 15 in H2:H5:

=AVEDEV(H2:H5)

Expected result: 4.5. The arithmetic mean is 6, and distances 4, 3, 2 and 9 total 18.

The median of this dataset is 3.5. Absolute distances from that different center are 1.5, 0.5, 0.5 and 11.5, with mean 3.5. Taking the median of those distances gives 1. These are different statistics: AVEDEV does not calculate either median-centered result.

Be precise about the phrase “absolute deviation.” The center and the final averaging operation both matter. See MEDIAN for the middle-of-ordered-data calculation.

Replace the original 12 with 24 in a copy of A2:A5. The new mean is 9, the distances are 7, 5, 3 and 15, and their mean is 7.5.

Do not update only the last distance: changing an observation also changes the center used by the other three distances. This explains why a helper-cell reproduction should anchor every row to the same recalculated mean.

On a separate sheet, enter numeric 2, text ‘8, numeric 6, =TRUE() and a genuinely empty cell in A2:A6. =AVEDEV(A2:A6) has expected result 2, using numeric observations 2 and 6.

Converting the text 8 into a real number makes the mean 16/3 and the expected AVEDEV 20/9, approximately 2.222222222. The result changes because the observation set changed, not because its display looked different.

The source does not provide a complete native error, empty-dataset or overflow table. Those cases remain unverified. ABS and AVERAGE are useful separate controls, but a mathematical reconstruction is not evidence of native WPS execution.

• WPS Academy: AVEDEV lesson, 2019-05-30 (Chinese; Windows Personal scope).
• Editorial testing standards.

Worked outputs are independently calculated expectations, not captured native results. The dated source establishes the stated product scope, not every current client or build.