zero in a range

[kevin carter]

hi
i have a column of data

coloum A
1 1
2 2
3 0
4 5
5 0
6 7
what i want to do is muliply them together
ie a1*a2*a3*a4*a5*s6 this will return zero because of the zero vaues
in A3 and A5

is it possible to ignore the zero values

thanks

kevin

 

[keiji kounoike]

What about this?

{=PRODUCT(IF(A1:A7=0,1,A1:A7))}

this is a array formula, so you need to press Shift+Ctrl+Enter instead
of just pressing Enter key.

Keiji

 

[kevin carter]

What about this?

{=PRODUCT(IF(A1:A7=0,1,A1:A7))}

this is a array formula, so you need to press Shift+Ctrl+Enter instead
of just pressing Enter key.

Keiji







- Show quoted text -

thanks
works a treat

kevin

 

[kevin carter]

thanks
works a treat

kevin- Hide quoted text -

- Show quoted text -

Another Question
When everything in the range is zero it returns 100% which is correct
using formula
can this formula be altered to show zero if everything is zero

thanks again

kevin

 

[keiji kounoike]

One way

{=IF(SUM(A1:A7)=0,0,PRODUCT(IF(A1:A7=0,1,A1:A7)))}

Keiji

 

[keiji kounoike]

I assumed all data is positive. if this is not the case, my formula
doesn't work correctly. try a formula below in that case.

{=IF(AND(A1:A7=0),0,PRODUCT(IF(A1:A7=0,1,A1:A7)))}

Keiji

 

[kevin carter]

I assumed all data is positive. if this is not the case, my formula
doesn't work correctly. try a formula below in that case.

{=IF(AND(A1:A7=0),0,PRODUCT(IF(A1:A7=0,1,A1:A7)))}

Keiji





- Show quoted text -

thanks again

works a treat

kevin

 

[keiji kounoike]

Thank you for your feedback.

thanks again

works a treat

kevin