Use an OR Condition Correctly in an Excel AVERAGE Formula in WPS Office
Question details
The user needs to average column C values only for rows where column A matches cell E3 and column B is either 9 or blank, without OR evaluating the entire range as a single condition.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is building a conditional average over rows that meet matching, blank, or equal-to-9 criteria.
- Observed behavior
- A formula using OR evaluates the range as one TRUE/FALSE result, so the AVERAGE formula returns an incorrect result because the condition is not applied row by row.
Confirm that the data ranges in columns A, B, and C are aligned and cover the same rows. Check whether your version of WPS Office Excel requires legacy array formula entry with Ctrl+Shift+Enter before applying the corrected formula.
Use element-by-element arithmetic inside AVERAGE and IF
Use this formula when you need OR logic applied per row inside an AVERAGE calculation without creating extra helper columns.
The OR function is not reliable in this scenario because it can return a single TRUE/FALSE result for the entire range instead of testing each row separately. Multiplying and adding the condition arrays applies AND and OR logic element by element.
Click the cell where you want the conditional average to appear, such as F3 or the first result cell in your summary table.
Type =AVERAGE(IF(($A$10:$A$1048576=$E3)*(($B$10:$B$1048576="")+($B$10:$B$1048576=9)),$C$10:$C$1048576)). The asterisk acts as AND and the plus sign acts as OR across per-row TRUE/FALSE results.
In older WPS Office Excel versions, press Ctrl+Shift+Enter instead of Enter. In newer dynamic array versions, pressing Enter is sufficient.
Check a few matching rows manually to confirm that column C values are averaged only when column A equals E3 and column B is blank or 9.
Create a helper column and use AVERAGEIF
Use this approach when you prefer a non-array formula, need to audit row-level conditions visually, or are working with a long-term report that other users must maintain.
Fix OR Logic in Excel AVERAGE Formulas with WPS Office
Open the workbook in WPS Office and replace the incorrect OR-based AVERAGE formula with an element-by-element array calculation. WPS Office Excel supports AVERAGE, IF, OR, AND and AVERAGEIF, so you can correct the formula directly in a familiar spreadsheet environment.
- 1. Open the workbook in WPS Office: Launch WPS Office and open the .xlsx workbook that contains the incorrect AVERAGE formula.
- 2. Select the formula cell: Click the cell where the corrected conditional average should be displayed.
- 3. Enter the row-by-row condition formula: Type =AVERAGE(IF(($A$10:$A$1048576=$E3)*(($B$10:$B$1048576="")+($B$10:$B$1048576=9)),$C$10:$C$1048576)).
- 4. Confirm the array formula correctly: Press Enter in dynamic array versions of WPS Office Excel, or press Ctrl+Shift+Enter in legacy array versions.
- 5. Verify and save the workbook: Compare the result with a manual sample check, then save the workbook in native .xlsx format.
Frequently Asked Questions
Why does OR return the wrong result in this AVERAGE formula?
OR can evaluate the entire range as one TRUE/FALSE condition instead of returning separate TRUE/FALSE results for each row. Use multiplication for AND and addition for OR to test the conditions element by element.
Do I need to press Ctrl+Shift+Enter in WPS Office Excel?
In older versions of WPS Office Excel, yes: confirm the formula with Ctrl+Shift+Enter. In newer dynamic array versions, you can normally press Enter. Check the formula result and whether Excel requests array entry to confirm your version.
Can I solve this without an array formula?
Yes. Insert a helper column and enter =AND(A10=$E3,OR(B10=9,B10="")) in the first data row, fill it down, then use =AVERAGEIF($D$10:$D$1048576,TRUE,$C$10:$C$1048576).
What do the asterisk and plus sign mean in the corrected formula?
The asterisk works like AND and the plus sign works like OR on arrays of TRUE/FALSE values. This creates a per-row test so only matching rows are included in the AVERAGE.
