Zoho Sheet AVEDEV: Average Absolute Deviation with Examples

AVEDEV(number; [number1]; ...)

The displayed semicolons are Zoho’s documented argument separators. You can use one contiguous range or provide separate values, for example =AVEDEV(12;-19;10;15). That exact four-number expression is also an example in Zoho’s official help and the documented result is 11.75. There is no separate mode parameter or percentile argument.

Zoho’s AVEDEV reference says that nonnumeric text is ignored, while text representing a number is an exception to that rule. It does not spell out every conversion case, such as a text numeral imported from CSV, logical constants entered directly versus referenced logical cells, error values within ranges, or formula-generated empty strings. Do not infer all those outcomes from this one sentence. For a reproducible analysis, use verified numeric cell values and a clearly defined missing-data policy.

This reference describes the current public Zoho Sheet function, not a historical minimum version or guaranteed feature parity across the web app, mobile clients, and desktop applications. No first-supported release is claimed.

Start a worksheet with the header Week in A1 and Units in B1. Enter the values below as actual numbers (without a leading apostrophe) in B2:B9.

1. Calculate the centre of the eight numbers with =AVERAGE(B2:B9) in E2. The sum is 180, so the expected mean is 180 ÷ 8 = 22.5.
2. In E3 enter =AVEDEV(B2:B9). The expected result is 7.625.
3. For an audit trail, put the mean (22.5) in E2 and enter =ABS(B2-$E$2) in C2; copy C2 down to C9. Those eight absolute distances should read 12.5, 8.5, 4.5, 4.5, 0.5, 3.5, 7.5 and 19.5.
4. Their sum is 61. Check the arithmetic with =SUM(C2:C9)/COUNT(B2:B9); it should be 61 ÷ 8 = 7.625. This last calculation verifies the arithmetic convention independently of the AVEDEV function.
5. Interpret that output in the same units as your inputs: 7.625 units of mean absolute deviation, not 7.625%, not dollars, and not a prediction interval.

These are expected results calculated from the table. The worksheet formulas were not run inside a native Zoho Sheet workbook during preparation.

Let x̄ be the arithmetic mean of the included numbers and n their count. The average absolute deviation about the mean is:

( |x1 − x̄| + |x2 − x̄| + ... + |xn − x̄| ) / n

For the table above, the positive distances total 61 and the number of observations is 8. Dividing yields 7.625. The absolute-value bars matter: ordinary signed deviations from the mean total zero and would make a poor indicator of variability. The denominator is n for this descriptive mean absolute deviation; do not substitute n−1 merely because sample variance and sample standard deviation use that denominator.

An important distinction: AVEDEV measures absolute distance from the dataset’s own mean. It is not the average absolute forecast error against an external forecast, nor is it the median absolute deviation (whose centre and aggregation differ). If a forecast is in column D, an error metric should compare actual values with D, not replace that comparison with AVEDEV of actuals alone.

Suppose a small reseller wants to know whether recent weekly demand is less stable than the previous period. Keep the same eight values and compute these independent periods:

The newer period has twice the within-period absolute variation: 6 versus 3. Notice that the full eight-week AVEDEV is even larger (7.625), partly because the demand level itself rose between the periods. That is why comparing periods at different means deserves care. For demand planning, present the mean and AVEDEV together, instead of interpreting the latter in isolation.

You can also build an explanatory flag in E4:

=IF(AVEDEV(B2:B9)>7;"Review variability";"Below review threshold")

For this illustrative threshold of 7 units, the expected text is Review variability. The threshold is a business rule chosen for demonstration, not a statistically validated Zoho recommendation. A better operating policy sets a threshold relative to fulfilment capacity or historical service levels.

Squaring gives large outliers a greater influence than absolute deviation does. A 20-unit deviation adds 20 to an absolute-distance sum but 400 to a squared-distance sum. That can be useful for detecting spikes but is a different measurement choice. Likewise, neither AVEDEV nor STDEV should be treated as the percentage of orders that arrive late; that is a rate calculation requiring a numerator and denominator defined around delivery status.

If you want a measure robust to unusually extreme weeks, consider MEDIAN or a separately defined median absolute deviation analysis. The built-in AVEDEV function itself is centred on a mean, not a median.

The result seems too small or too large. Confirm that all expected cells contain genuine numbers. Inspect zeroes separately from missing values: zero is a legitimate numerical observation and can change the average and all distances, while replacing “unknown” with zero changes the meaning of the dataset. Also check for a one-time promotion or stockout that made the mean unusually high or low.

Some entries are text. Zoho documents that ordinary text is excluded from AVEDEV and discusses an exception for numeric-looking text. If B5 says pending, exclude it only if that represents genuinely unavailable data; do not put a business-critical “missing” status into a numerical series without a plan. Different conversions of text numerals are an unverified native-test item. Clean the source or enter numeric values deliberately.

A formula shows #NAME!. Check the spelling AVEDEV, range colons and quote marks. This is among Zoho’s listed generic possible errors. A comma-versus-semicolon mismatch is worth reviewing against the actual application locale and vendor syntax.

A formula shows #REF!. An input cell or range may have been deleted or altered. Recreate the intended reference and ensure that copied formulas refer to the correct rows.

A formula shows #VALUE! or #N/A!. Zoho’s help lists these as possible errors, but provides generic explanations rather than a complete AVEDEV-specific error matrix. Check typed values and upstream error cells; do not promise that a particular mixed-value combination produces one exact code without native testing.

There are zero or one valid numbers. Mathematically, mean absolute deviation for a single observed number is zero, but the vendor page does not specifically establish the behaviour of every empty, all-text, or one-cell input case. Validate these cases in the target Zoho Sheet client before declaring a production data-quality rule. Use an independent COUNT check to detect whether the input contains enough meaningful numeric observations for your policy.

The source contains live formulas. Dynamic references can change over time. Record the period and source revision if the result is used for operational decisions; the output alone does not identify which historical values were included.

Zoho’s official help writes =AVEDEV(12;-19;10;15) with semicolons. Microsoft’s English-language Excel reference presents AVEDEV(number1, [number2], ...) with commas, and Google’s published Sheets reference does likewise. Actual separators can also depend on locale. Keep the separators appropriate to the application instead of blindly pasting a formula string.

Microsoft’s Excel guidance explicitly distinguishes direct literal arguments from values inside arrays/references, including treatment of logical entries; Google’s help explicitly says AVEDEV ignores text. Zoho provides a shorter text-exclusion remark with a numeric-text exception. These descriptions are not enough to assert identical conversion behaviour for every mixed-type input. For a reproducible comparison, use validated numerical observations in all three applications and separately test questionable types. Excel documents a 255-argument limit on its AVEDEV page; this does not establish a Zoho limit. No identical historical-version or mobile-platform availability is assumed.

Directly checked references (October 12, 2026):

• Zoho Sheet — AVEDEV — primary syntax, description, example and error list.
• Microsoft Excel — AVEDEV — comparison of syntax and type handling.
• Google Sheets — AVEDEV — comparison of function description and syntax.

AVERAGE explains the centre used in AVEDEV; DEVSQ, VAR and STDEV explain alternative spread measurements.

Verification status: Official Zoho Sheet documentation checked October 12, 2026. The reported numbers were independently calculated from the explicit sample data. Not executed in Zoho Sheet. Direct or indirect execution in another spreadsheet application is not claimed.

No genuine Zoho Sheet screenshots are included; this guide does not use mock application images. Native confirmation is still required for text-as-number handling, all-empty ranges, logical-value handling, and client/version-specific error displays.