Formula Errors

Excel Formula: How to Handle Blank and Zero Values in Two Cells

Natalie Taylor Sep 27, 2026 869 views

Question details

The user needs an Excel formula for cell BN9 that performs a weighted calculation based on two other cells (BJ9 and BL9), but returns a blank if either of those reference cells contains a zero or is empty.

How to Handle Blank and Zero Values in Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Performing a conditional weighted calculation in a spreadsheet where missing or zero data should result in an empty cell rather than a skewed result or error.
Observed behavior: The formula needs to evaluate two cells, outputting a blank cell when either is zero or empty, and executing the calculation (BL9 + 3 × BJ9) ÷ 4 only when both contain non-zero numbers.

Before you start

Identify the exact cell references you are working with and verify that the target cells contain numeric data rather than hidden spaces or text formatting.

Solution 1Recommended

Use the IF and OR Functions to Evaluate Cells

By combining the IF and OR functions, you can check multiple cells simultaneously and conditionally output a blank string or a calculated formula result.

The IF function is designed to return different results depending on whether a specific condition is met. By nesting the OR function inside IF, you can tell Excel to output a blank cell if either BJ9 or BL9 equals zero.

Select the destination cell

Click on cell BN9 (or whichever cell you want the calculation result to appear in).

Enter the IF and OR formula

Type the formula =IF(OR(BJ9=0,BL9=0),"",(BL9+3*BJ9)/4) into the formula bar.

Apply the calculation

Press Enter. The cell will display the weighted calculation if both cells have valid non-zero numbers, or remain completely blank if either cell is 0 or empty.

Use the IF and OR Functions to Evaluate Cells

Handling Empty Cells: In Excel logical comparisons, completely empty cells are automatically treated as zero. Therefore, checking if the cell equals 0 successfully captures both zeroes and blank cells without needing additional functions.

Easily Handle Advanced Spreadsheet Formulas with WPS Office

WPS Spreadsheet fully supports Excel's IF, OR, and complex logical functions. You can seamlessly calculate weighted averages, manage empty data cells, and analyze datasets in a highly intuitive environment.

1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
2. Select your target cell: Click the specific cell where the calculation result should be displayed.
3. Input the logical formula: Type =IF(OR(BJ9=0,BL9=0),"",(BL9+3*BJ9)/4) and press Enter to instantly apply the condition.
4. Apply to multiple rows: Hover over the bottom right corner of the cell and drag the fill handle down to apply this logic to the rest of your dataset.

100% compatible with Microsoft Excel formulas, functions, and file formatsFast, lightweight, and handles large datasets without laggingBuilt-in advanced data analysis and visualization toolsFree to download and use for your daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

How can I make the formula ignore blanks but still calculate if the cell contains an actual zero?

If you need to treat a typed 0 as a valid number but leave blanks empty, use the ISBLANK function instead. Your formula would look like this: =IF(OR(ISBLANK(BJ9), ISBLANK(BL9)), "", (BL9+3*BJ9)/4). This ensures that only truly empty cells trigger the blank output.

Why does my formula display an error instead of a blank cell?

If your formula returns an error like #VALUE!, one or both of the referenced cells may contain hidden text, spaces, or unrecognized characters. Ensure both cells are properly formatted as numbers and clear any invisible space characters.

Can I use IFERROR to handle blank cells instead of the IF function?

No, IFERROR is designed specifically to catch calculation errors (such as dividing by zero or reference errors). Because Excel treats blank cells as valid zeroes in basic formulas, it won't trigger an error flag in a standard addition or multiplication equation. You must use IF to evaluate the cell's contents directly.

Excel & Data Problems