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


The information in this article applies to:

  • Microsoft Excel 2000
  • Microsoft Excel 2002
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q61892

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. 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

=PRODUCT(A4*1,B4*1)

will return a value of zero, rather than 10.5 as shown above.
Modification Type:MajorLast Reviewed:5/13/2003
Keywords:kbhowto KB61892
©2002 Microsoft Corporation. All rights reserved.