MEDIAN in WPS Spreadsheet: Find the Middle Value and Handle Ties
Calculate WPS MEDIAN for odd and even datasets, distinguish numeric zero from ignored reference types, and avoid averaging subgroup medians.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: MEDIAN returns the middle of an ordered numeric dataset. With an odd number of observations it selects the central value; with an even number it averages the two central values. Repeated observations remain part of the dataset.
Availability and version history
- Introduced: documented by 2019-05-27 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.
Syntax and included observations
=MEDIAN(number1, [number2], ...)
Provide at least one value or reference. The 2019 Windows Personal lesson describes 1–30 arguments; the current build’s maximum is not established here. One range argument can contain several observations.
Inside arrays or references, text, logical values and genuinely empty cells are ignored, while numeric zero is included. These documented reference rules are not a complete direct-argument coercion table. Examples use English names, comma separators and explicitly numeric input cells.
Example 1: find the middle of five observations
Enter 4, 7, 9, 12 and 30 in A2:A6:
=MEDIAN(A2:A6)
Expected result: 9, the third value when the five observations are ordered. There are two observations before it and two after it.
If the cells instead contain 30, 4, 12, 9 and 7, the expected median is still 9. The order statistic depends on the values, not their original worksheet order. The function does not rearrange the source cells or identify the record associated with the returned value.
For comparison, =AVERAGE(A2:A6) gives 12.4 on the original five values. The arithmetic mean and median answer different questions about the same dataset.
Example 2: an even dataset can produce an unobserved value
Add numeric 100 in A7:
=MEDIAN(A2:A7)
Expected result: 10.5, the mean of the third and fourth ordered observations, 9 and 12. No source record needs to contain 10.5.
The arithmetic mean of these six values is 27. A central-position summary should not be described as the total divided by the number of records unless the two central values happen to produce that same result.
Example 3: repeated values occupy repeated positions
On a separate sheet put 4, 7, 7, 9 and 30 in A2:A6:
=MEDIAN(A2:A6)
Expected result: 7. Removing the duplicate 7 would leave four values and change the median to 8. Deduplication changes the question from the middle observation to the middle of distinct values.
Repeated observations can be legitimate. Decide whether the data contains repeated measurements or accidental duplicate records before changing it merely to make a summary look different.
Advanced example: inspect stored types and zero
Create this reference control in D2:D7:
| Cell | Entry |
|---|---|
| D2 | Number 0 |
| D3 | Number 10 |
| D4 | Text '100 |
| D5 | =TRUE() |
| D6 | Leave genuinely empty |
| D7 | Number 20 |
=MEDIAN(D2:D7)
Expected result: 10, using numeric values 0, 10 and 20. The quoted numeric-looking text and logical value do not add observations under the referenced-range rule.
Clearing D2 completely leaves 10 and 20, giving 15. If instead you retain zero and convert D4 to the actual number 100, the four numeric observations become 0, 10, 20 and 100; their median is also 15, for a different reason. Inspect both the included values and their count.
Advanced example: do not average subgroup medians
Group one contains 1, 2 and 100, whose median is 2. Group two contains 10, 11, 12, 13 and 14, whose median is 12.
Averaging the subgroup medians gives 7, but the combined ordered data is 1, 2, 10, 11, 12, 13, 14, 100. Its median is 11.5. Even weighting the two medians by group size gives 8.25, which still does not recover the combined median.
Put the two groups in F2:F4 and G2:G6 and use:
=MEDIAN(F2:F4,G2:G6)
Expected result: 11.5. To combine medians correctly in general, retain the underlying observations or enough ordering information; subgroup medians alone discard relevant positions.
Advanced check: distinguish resistance from immunity
In the original five-observation dataset, replacing 30 with 100 leaves the median at 9, while the mean increases from 12.4 to 26.4. The changed largest value does not cross the middle position.
This finite example does not mean a median never changes when data changes. Adding observations, changing central values or changing which records are included can move it. For example, the earlier addition of 100 changed the count from five to six and the median from 9 to 10.5.
Errors, interpretation and related measures
The captured lesson does not establish a complete native error, direct-text or no-numeric-data result table. Do not silently replace an undefined summary with zero. The function describes a numeric center, not the quality or completeness of the source data.
SMALL and LARGE inspect individual ordered positions, while AVERAGE calculates an arithmetic mean. The correct choice depends on the reporting question and inclusion rule.
References and verification
- WPS Academy: MEDIAN lesson, 2019-05-27 (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.