AGGREGATE in WPS Spreadsheet: Find a Maximum While Ignoring Errors

The official WPS AGGREGATE lesson documents the behavior used here. It identifies WPS Office for Windows Personal. Reviewed October 19, 2026, this Chinese-language evidence does not establish an exact introduction build, every current edition, or English-global, Mac, Linux, web or mobile behavior. No new WPS execution or native screenshot was possible; the examples are independent controls or explicitly conditional fixtures.

=AGGREGATE(function_num,options,ref1,[ref2],...)

This is the reference form documented by WPS. The source also lists an array form with an additional k argument for some operations, but that separate contract is not exercised here.

• function_num: selects the aggregation operation. This guide uses 4 = MAX, confirmed in both the source’s function table and its narrative example.
• options: selects what to ignore. This guide uses 6 = ignore error values, confirmed in the same two places.
• ref1: the source range containing the values to aggregate.
• Additional references: the source documents these, but the examples intentionally use one simple range.

The option number and function number serve different purposes. Reversing 4 and 6 does not request the same calculation.

Enter these exact cells:

In C2 enter =AGGREGATE(4,6,A2:A6). The source-supported, independently calculated expectation is 20, since the non-error numeric values are 12, 7, 20 and 4.

For a comparison, =MAX(A2:A6) includes the error cell rather than selecting AGGREGATE’s ignore-errors option. The WPS lesson explicitly contrasts MAX’s error propagation with this AGGREGATE use case. Ignoring an error is a deliberate reporting decision, not a repair of its cause.

This layout is useful for a temporary import-quality review where valid measurements must remain visible while faulty rows are investigated. Keep the error row available for follow-up instead of silently treating it as a genuine zero.

On a separate sheet, enter -8 in A2, =1/0 in A3, -3 in A4 and -12 in A5. =AGGREGATE(4,6,A2:A5) has independently expected result -3.

The control is max(-8,-3,-12) = -3. This example distinguishes ignoring the error from replacing it with zero, which would incorrectly change this maximum to 0. If your business rule requires a replacement value instead, define that transformation explicitly before aggregating.

The WPS page’s option-4 description and its missing-k discussion contain inconsistencies. In particular, the missing-k example’s prose and result column disagree about the error code. Neither disputed detail is needed for MAX with option 6, so they are not promoted into a general compatibility table here.

The source also cautions that calculated array arguments can change which hidden rows and nested aggregates are ignored. A plain reference like A2:A6 is not evidence for an expression such as A2:A6*(A2:A6>0). Do not extend these results to calculated arrays or hidden-row rules without separate verification.

• Confirm 4,6 rather than assuming all AGGREGATE modes ignore errors.
• Verify that error cells contain actual formula errors, not text that merely looks like an error.
• Exclude the header and keep numbers numeric.
• No behavior is established here for all-error ranges, 3D references or missing required k arguments.
• Use MAX when ordinary error propagation is appropriate.
• Review SUBTOTAL separately for visibility-aware reports. Option 6 in this guide is an ignore-errors request, not a hidden-row policy.

AGGREGATE does not certify data completeness. A high value can be correct for the valid subset while important invalid records remain unresolved.

• WPS AGGREGATE lesson

Expected outputs are derived from the explicit fixtures and documented operation. They are not results observed in an installed WPS build. Check the intended edition before relying on these formulas in a shared workbook.