Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

XL: PRODUCT Function Evaluates a Blank Cell as 1, Not Zero

View products that this article applies to.


The Microsoft Excel PRODUCT function treats a blank cell as if it has the value of 1.

↑ Back to the top

More information

The following worksheet is an example:
   A1: 10.5   B1: 2     C1: =PRODUCT(A1,B1)
   A2: 10.5   B2: 0     C2: =PRODUCT(A2,B2)
   A3:        B3:       C3:
   A4: 10.5   B4:       C4: =PRODUCT(A4,B4)
The formulas in C1, C2, and C4 return 21, 0, and 10.5, respectively. The blank cell in B4 is computed as the value 1 so that the PRODUCT function will not return a value of 0 when used to sum a range of cells that includes blank cells. You can force the PRODUCT function to evaluate blank cells as zero by adding *1 to each argument within the function. For example, the formula
will return a value of zero, rather than 10.5 as shown above.

↑ Back to the top

Keywords: KB61892, kbhowto

↑ Back to the top

Article Info
Article ID : 61892
Revision : 3
Created on : 9/19/2011
Published on : 9/19/2011
Exists online : False
Views : 494