Math
PRODUCT function: multiply chained yield factors and sequential discounts
Multiply an entire column of growth multipliers or manufacturing stage yields with PRODUCT while skipping blank and text cells.
Why PRODUCT beats chained * operators on ranges
Chaining =B2*B3*B4*B5 breaks with #VALUE! if any cell contains a note like "Pending", and fails to include newly inserted rows. PRODUCT(number1, [number2], ...) multiplies every numeric cell in a range while ignoring blanks and text.
Multiplying yearly growth factors 1.10 * 0.90 * 1.20 on a $1,000 starting balance gives $1,000 * 1.188 = $1,188.00.
Ignoring a Pending factor gives the result for known periods only. It does not forecast the missing period; keep that distinction visible in a report.
| Stage (A) | Factor (B) | InitialPrincipal (D) |
|---|---|---|
| Year 1 (+10%) | 1.1 | 1000 |
| Year 2 (-10%) | 0.9 | |
| Year 3 (+20%) | 1.2 | |
| Year 4 (TBD) | Pending |
Compound principal across numeric factors
PRODUCT(B2:B5) ignores "Pending" in B5 and multiplies 1.1 * 0.9 * 1.2 = 1.188, turning $1,000 in D2 into $1,188.
=ROUND(D2 * PRODUCT(B2:B5), 2)A zero cell vs a blank cell
Whereas a blank or text cell is ignored (effectively treated as 1), an explicit 0 in the range drives the entire PRODUCT to 0.
=PRODUCT(B2:B5)Try the example
Use the original values or change them to test your understanding.