Ignore 0 value when multiplying

[Antje Crawford]

Hello,
I have a range (A1:A5) of cells that calculate the
percentage of another range. If any of the values in A1:A5
is zero, the result will be zero also. I would like to
calculate the product of this range (A1:A5) but ignore any
zero value. Is there a way to do so?

All help is greatly appreciated.
TIA.
BR, Antje Crawford

 

[Paul]

Hello,
I have a range (A1:A5) of cells that calculate the
percentage of another range. If any of the values in A1:A5
is zero, the result will be zero also. I would like to
calculate the product of this range (A1:A5) but ignore any
zero value. Is there a way to do so?

All help is greatly appreciated.
TIA.
BR, Antje Crawford

=PRODUCT(IF(A1:A5<>0,A1:A5))
Array-entered (that is, Ctrl+Shift+Enter rather than just Enter).

 

[Antje Crawford]

Paul,
thank you very much. It is working very nicely.
BR, Antje

 

[Harlan Grove]

...
...

=PRODUCT(IF(A1:A5<>0,A1:A5))
Array-entered (that is, Ctrl+Shift+Enter rather than just Enter).

Another array formula that uses only one function call is

=PRODUCT(A1:A5+(A1:A5=0))

 

[Jay Petrulis]

...
..

Another array formula that uses only one function call is

=PRODUCT(A1:A5+(A1:A5=0))

Clever. This works without array entry. Nice one.

 

[Paul]

Harlan Grove<[email protected]> wrote in message

Clever. This works without array entry. Nice one.

I agree that it's an elegant formula and I thank Harlan for posting it .
However, Harlan didn't claim that it worked without array-entry, but that it
uses only one function call. He specifically described it as "another array
formula". I think you will find your assetion "This works without array
entry" is wrong. Have you tried it?

 

[Jay Petrulis]

I agree that it's an elegant formula and I thank Harlan for posting it .
However, Harlan didn't claim that it worked without array-entry, but that it
uses only one function call. He specifically described it as "another array
formula". I think you will find your assetion "This works without array
entry" is wrong. Have you tried it?

I stand corrected. I tested it at could've sworn that I used both
array entry and non-array entry with identical results. I just tested
it again without array entry and got a #VALUE! error, as expected.