PRODUCT counting empty cells as 1

S

Supersonic

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?
 
B

bj

"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
 
B

Bob Phillips

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)
 
S

Supersonic

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