PRODUCT counting empty cells as 1

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When multiplying different cells - or columns of cells - by inserting the
function PRODUCT in the cell for the result, any empty cells count as 1 in
the function...

Say for example, a cell (A1) representing a number of items multiplied with
another cell (A2) representing the cost per item resulting in a third cell
(A3) representing the total cost.
Inserting the function PRODUCT in cell (A3) and entering (A1) and (A2) makes
(A3) display the value of (A1) if (A2) is empty; counting the empty cell as 1.
You would instead expect (A3) to be 0 if (A2) or (A1) is empty...

If however you manually enter [=(A1*A2)] in the function field for cell
(A3), the displayed result in (A3) is 0 if any of the other cells is empty.

Thus there seems to be a significant difference between the two ways of
doing a multiplication.
Is this a bug or a feature?
 
"If an argument is an array or reference, only numbers in the array or
reference are counted. Empty cells, logical values, text, or error values in
the array or reference are ignored. "

from the help section on Product
 
Try this

=IF(OR(A1="",A2=""),"",PRODUCT(A1,A2))

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Empty cells, logical values, text, or error values in
the array or reference are ignored. "

from the help section on Product
Click to expand...

Indeed so, I was just surprised to find a) that ignoring obviously equals
multiplying by 1, and b) that these two ways of performing a multiplication
handles empty cells differently.
 
Back
Top