Ignore 0 value when multiplying

  • Thread starter Thread starter Antje Crawford
  • Start date Start date
A

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
 
Antje Crawford said:
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
Click to expand...

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

Another array formula that uses only one function call is

=PRODUCT(A1:A5+(A1:A5=0))
 
Harlan Grove said:
...
..

Another array formula that uses only one function call is

=PRODUCT(A1:A5+(A1:A5=0))
Click to expand...

Clever. This works without array entry. Nice one.
 
Jay Petrulis said:
Harlan Grove<[email protected]> wrote in message

Clever. This works without array entry. Nice one.
Click to expand...

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?
 
Paul said:
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?
Click to expand...

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