Ignore Zeros in Excel Multiplication 1

[krispi]

Hi all

I have a formula which multiplies together a number of percentages (too complicated to explain why!), so an example would be:

=O3*P3*Q3*R3*S3*T3*U3

Obviously, if one or more of the cells contains zero, the result of the calculation is zero. Is there any way (preferably without performing a separate test on each cell within the formula) to automatically ignore zeros so that only cells containing a positive value are included? (Basically the equivalent of SUMIF, but for multiplication)

Can I do this in an array?

Any help gratefully appreciated.

Chris

 

[GlennUK]

An array formula ( that is entered using Ctrl-Shift-Enter instead of Enter ) like this should do it:

=PRODUCT(IF(O3:U3>0,O3:U3,1))

Entering a formula using Ctrl-Shift-Enter ( that's CSE for short ) ... also known as the Vulcan Death Grip, automatically puts curly brackets around the formula, to signify that it's an array formula.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[krispi]

Cheers Glenn, works like a dream - have a star!