Difference PRODUCT function and Multiply cells

[JanBolhuis]

Hi all,

If the formulas of the attachement won't work in your version, please replace the semicolons by a comma.

I tried to figure out the difference between the PRODUCT function and the manual multiplying of the same cells. The HELP of Excel tells me that PRODUCT(A1,A2,A3,A4) should have the same result as A1 * A2 * A3 * A4. But... my tests are saying something difference.

So I tested it in three variations. Can someone explain to me what the PRODUCT function exactly does and why the result in variation 2 is not equal to eachother? Because it seems that PRODUCT doesn't see a zero when the cell is blank.

Variation 1: Values of A1, A2, A3, A4 are 1, 0, 0, 0
= PRODUCT( A1, A2, A3, A4 ) >> result 0
= A1 * A2 * A3 * A4 >> result 0

Variaton 2: Values of B1, B2, B3, B4 are 1, blank cell, blank cell, blank cell
= PRODUCT( B1, B2, B3, B4) >> result 1
= B1 * B2 * B3 * B4 >> result 0

Variaton 3: C1, C2, C3, C4 are all blank cells
= PRODUCT( C1, C2, C3, C4) >> result 0
= C1 * C2 * C3 * C4 >> result 0

Thanks

 

[Hui]

From Excel help
If an argument is an array or reference, only numbers in the array or reference are multiplied. Empty cells, logical values, and text in the array or reference are ignored.

You may want to try
=Product(A1:A4)
Etc

 

[NARAYANK991]

Hi Jan ,

It is exactly the same with the following 3 variations :

=A1 + B1

=SUM(A1 , B1)

=SUM(A1 + B1)

Try having the following in A1 and B1 :

A1 : 1

B1 : blank

B1 : text

B1 : 0

B1 : =1=1

B1 : =1=0

Narayan

 

[JanBolhuis]

From Excel help
If an argument is an array or reference, only numbers in the array or reference are multiplied. Empty cells, logical values, and text in the array or reference are ignored.

You may want to try
=Product(A1:A4)
Etc

Working for years with Excel, run a website about it and have never noticed this. Unbelievable Another lesson learned. Thanks.

 

[JanBolhuis]

Hi Jan ,

It is exactly the same with the following 3 variations :

=A1 + B1

=SUM(A1 , B1)

=SUM(A1 + B1)

Try having the following in A1 and B1 :

A1 : 1

B1 : blank

B1 : text

B1 : 0

B1 : =1=1

B1 : =1=0

Narayan

Thank you too. It's indeed the same.