How to Use MEDIAN and MODE with IF Conditions and Blank Cells in WPS Office Spreadsheets
Question details
The user needs to calculate median and mode for a numeric range where another column matches a specific text condition and blank numeric cells are excluded from the result.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a dataset with a weekday column and numeric values, and wants to return the median and mode for Monday values while ignoring blank cells.
- Observed behavior
- The formulas must treat blank cells and text as ignored, include zero values, and may require legacy Ctrl+Shift+Enter entry or a modern dynamic array formula depending on the WPS/Excel version.
Open your workbook in WPS Office Spreadsheets and locate the condition column and the numeric value column. Confirm the exact range references, for example B2:B49 for weekdays and F2:F49 for numbers, before entering the formulas.
Use an IF array formula to calculate MEDIAN and MODE with a condition
Best for legacy file compatibility and versions where dynamic array formulas are not available.
This formula combines a text condition and a non-blank check by multiplying logical arrays, then passes the qualifying values to MEDIAN or MODE. Because the formula returns an array, legacy WPS Spreadsheets and Excel versions require Ctrl+Shift+Enter.
Click the cell where you want the MEDIAN result to appear, for example G2.
Type =MEDIAN(IF((B2:B49="Monday")*(F2:F49<>""),F2:F49,"")) and press Ctrl+Shift+Enter on Windows or Command+Enter on Mac to confirm it as an array formula.
Click another empty cell and type =MODE(IF((B2:B49="Monday")*(F2:F49<>""),F2:F49,"")) then press the array formula shortcut.
Use TOCOL and FILTER for a dynamic array formula in newer WPS Office or Microsoft 365
Use this when your version supports dynamic arrays and you prefer a formula that does not require Ctrl+Shift+Enter.
Calculate Conditional MEDIAN and MODE in WPS Office Spreadsheets
Use WPS Office Spreadsheets to run the same MEDIAN and MODE array formulas with IF conditions and blank-cell exclusion. It supports legacy array entry and dynamic formulas while keeping your Excel files fully compatible.
- 1. Open WPS Spreadsheets: Open the workbook that contains the weekday and numeric value columns in WPS Office Spreadsheets.
- 2. Select the result cell: Click the cell where you want the conditional median to appear.
- 3. Enter the array formula: Type =MEDIAN(IF((B2:B49="Monday")*(F2:F49<>""),F2:F49,"")) and press Ctrl+Shift+Enter on Windows or Command+Enter on Mac.
- 4. Repeat for MODE: Enter =MODE(IF((B2:B49="Monday")*(F2:F49<>""),F2:F49,"")) in another cell with the same array shortcut.
Frequently Asked Questions
Can I use these MEDIAN and MODE formulas in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports the same IF array formula and, in recent versions, dynamic array functions such as FILTER and TOCOL.
Why does the formula include F2:F49<>""?
The condition F2:F49<>"" excludes blank cells from the array before MEDIAN or MODE is calculated, while text in the numeric range is also ignored by those functions.
Are cells containing zero included in the median and mode?
Yes. MEDIAN and MODE include numeric zero values; only blank cells, text, and logical values are ignored in the referenced numeric range.
Do I need to press Ctrl+Shift+Enter for the IF array formula?
In legacy Excel and older WPS Spreadsheets versions, yes. In Microsoft 365 or WPS versions that support dynamic arrays, you can enter the TOCOL/FILTER version without the array shortcut.
Can I add more than one condition to MEDIAN or MODE?
Yes. Multiply additional condition arrays inside the IF function, for example (B2:B49="Monday")*(C2:C49="Sales")*(F2:F49<>""), and confirm with the array shortcut if required.
