How to Average Excel Cells While Treating Blanks as 150 in WPS Office Spreadsheets
Question details
The user needs to calculate an average or sum in a range while treating every genuinely blank cell as the value 150.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a numeric range that contains blank cells and wants those blanks included in the average or sum as 150 instead of being ignored or counted as zero.
- Observed behavior
- Standard AVERAGE and SUM functions ignore truly blank cells, so the result does not reflect the intended 150 value for blanks.
Identify the range to evaluate and confirm that the cells you want to treat as 150 are genuinely empty, because cells containing spaces or zero-length strings are not detected by ISBLANK.
Calculate the Average Treating Blank Cells as 150
Use this formula when you want the average of a range and every blank cell must contribute 150.
The IF(ISBLANK(A1:A4),150,A1:A4) part returns 150 for each blank cell and the original value for each non-blank cell. AVERAGE then calculates the mean of that resulting set.
Click an empty cell where you want the average result to appear.
Type =AVERAGE(IF(ISBLANK(A1:A4),150,A1:A4)) and replace A1:A4 with your actual data range.
Press Enter in WPS Office, Microsoft 365, or Excel 2021. If your spreadsheet version does not automatically spill dynamic arrays, press Ctrl+Shift+Enter.
Calculate the Sum Treating Blank Cells as 150
Use this formula when the goal is a total sum rather than an average.
Use WPS Spreadsheets to Average Blank Cells as 150
WPS Office Spreadsheets supports the same AVERAGE, IF, and ISBLANK formulas, so you can replace blank cells with 150 inside the calculation without editing the source data.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook containing the range you want to average.
- 2. Select the result cell: Click the empty cell where the average result should appear.
- 3. Enter the formula: Type =AVERAGE(IF(ISBLANK(A1:A4),150,A1:A4)) and replace A1:A4 with your actual data range.
- 4. Confirm the calculation: Press Enter. If your WPS version prompts for array formula entry, press Ctrl+Shift+Enter.
- 5. Check the result: Review the returned average to confirm that blank cells were counted as 150.
Frequently Asked Questions
What formula averages blank cells as 150 in Excel or WPS Spreadsheets?
Use =AVERAGE(IF(ISBLANK(A1:A4),150,A1:A4)). Replace A1:A4 with your actual range. In WPS Office or Microsoft 365, press Enter; older versions may need Ctrl+Shift+Enter.
Why does my formula return an error or not calculate until I press Ctrl+Shift+Enter?
The IF(ISBLANK(...),150,range) formula creates an array result, so older spreadsheet engines require array formula entry. Select the cell, press F2, and then press Ctrl+Shift+Enter if your version does not spill dynamic arrays.
Does this formula change the original blank cells to 150?
No. The formula only replaces blanks with 150 inside the calculation. The original cells remain blank.
Can I treat cells with spaces as blank?
ISBLANK only detects truly empty cells. If cells contain spaces or formulas returning empty strings, clean those cells first or use a condition that matches your specific cell content.
How do I sum blank cells as 150 instead of averaging them?
Use =SUM(IF(ISBLANK(A1:A4),150,A1:A4)). It converts blank cells to 150 before summing the range.
