Logical
Return Blank ("") If Input Is Empty—Without Breaking Downstream Math!
Suppress ugly 0s on unentered template rows with IF(ISBLANK(B2), "", ...) while ensuring downstream totals use SUM/N() rather than + so "" never triggers #VALUE!.
Keep invoice templates clean on blank rows without crashing Grand Totals!
Pre-filling 25 invoice line-item rows with =B2 * C2 fills every unused row with unsightly $0.00 clutter! Guarding empty rows with =IF(OR(ISBLANK(B2), ISBLANK(C2)), "", ROUND(B2 * C2, 2)) keeps unentered rows visually blank ("")—while still displaying legitimate $0.00 free-sample lines where Quantity = 2 and UnitPrice = 0!
Notice two golden rules when returning "": 1) Use ISBLANK(B2) rather than B2 = "" if you need to distinguish a truly empty cell from 0 in loose-equality engines; and 2) Downstream formulas referencing a cell that may hold "" MUST use =SUM(D2:D5) or N(D2) rather than D2 + D3, because "" + 100 throws #VALUE! in Excel!
| Invoice Template Row | Qty (B) & Price (C) | Line Formula Output | Included in =SUM(D2:D5)? |
|---|---|---|---|
| Active Paid Line | Qty = 3, Price = $45 | 135 | Yes (+135) |
| 100%-Discounted Free Item | Qty = 2, Price = $0 | 0 (Shows real $0!) | Yes (+0) |
| Unentered Blank Row | Qty = (empty), Price = (empty) | "" (Clean blank!) | Skipped cleanly by SUM! |
Clean Template Line Calculation Returning "" Only When Unentered
Tests ISBLANK(B2) and ISBLANK(C2) so real 0 prices still display 0 while blank rows stay "".
=IF(OR(ISBLANK(B2), ISBLANK(C2)), "", ROUND(B2 * C2, 2))Safe Downstream Arithmetic on Potentially-Blank ("") Formula Cells
Uses SUM (or N(cell)) so empty-string "" line items never cause #VALUE! errors.
=SUM(D2:D4)Try the example
Use the original values or change them to test your understanding.