zero in a range

  • Thread starter Thread starter kevin carter
  • Start date Start date
K

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
 
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
 
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 -
Click to expand...

thanks
works a treat

kevin
 
thanks
works a treat

kevin- Hide quoted text -

- Show quoted text -
Click to expand...

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
 
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
 
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 -
Click to expand...

thanks again

works a treat

kevin
 
Back
Top