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

 Last reviewed: February 18, 1998
Article ID: Q61892
2.x 3.x 4.00 5.00 5.00c 7.00 | 2.20 2.21 3.00 | 2.x 3.00 4.00 5.00 
WINDOWS                      | OS/2           | MACINTOSH
kbusage

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

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.00 2.0 2.01 2.1 2.10 2.2 2.21 2.20 3.0
3.00 4.0 4.00 5.0 5.00
Keywords : kbusage
Version : 2.x 3.x 4.00 5.00 5.00c 7.00 | 2
Platform : MACINTOSH OS/2 WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 18, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.