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

ID: Q61892

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel for OS/2, versions 2.2, 3.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0

SUMMARY

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

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.

WORKAROUND

You can force the PRODUCT function to evaluate blank cells as zero by adding "*1" (without the quotes) to each argument within the function. For example, the formula


   =PRODUCT(A4*1,B4*1)

will return a value of zero, rather than 10.5 as shown above.

REFERENCES

"Online Help," version 5.0
"Function Reference," version 4.0, page 337
"Function Reference," version 3.0, page 167
"Functions and Macros," version 2.x, pages 96-97

Additional query words: 2.00 2.0 2.01 2.1 2.10 2.2 2.21 2.20 3.0 5.0

Keywords :
Version : 2.x 3.x 4.00 5.00 5.00c 7.00 | 2
Platform : MACINTOSH OS/2 WINDOWS
Issue type :