Ignoring charachter data with Product function
 

Hi all

in short:
I have row A of data that has numbers between -1 and 1 and some characters.
I need to find the value of Product(1+A:A). Using the function N to convert
chars to nums has not done the trick.

more detail:

I get a data dump of return numbers in percent from a third party software
that I must link to. Upon linking to a different spread sheet I use

=IF(DataIn!A1=0,"",DataIn!A1)

and drag this forumla to get rid of zeros in my linked data.

then I must find the total returns for all colums.
If I use Product(1+A1:A10) I get #Value since there are chars in the data
(even though Its only "" it does not work.) I must use Product(1+randge) not
simply product(range) since this is return data and I must add a 1 first to
find the correct return.
Any ideas?
Thanks!
Stan

Hope this helps
=PRODUCT(IF(ISNUMBER(A2:A8),(1+A2:A8)))
array entered(ctrl+shift+enter) instead of just enter

"Stan Altshuller" wrote in message
...
Hi all

in short:
I have row A of data that has numbers between -1 and 1 and some
characters.
I need to find the value of Product(1+A:A). Using the function N to
convert
chars to nums has not done the trick.

more detail:

I get a data dump of return numbers in percent from a third party software
that I must link to. Upon linking to a different spread sheet I use

=IF(DataIn!A1=0,"",DataIn!A1)

and drag this forumla to get rid of zeros in my linked data.

then I must find the total returns for all colums.
If I use Product(1+A1:A10) I get #Value since there are chars in the data
(even though Its only "" it does not work.) I must use Product(1+randge)
not
simply product(range) since this is return data and I must add a 1 first
to
find the correct return.
Any ideas?
Thanks!
Stan

Well Done, Thank a lot!

"N Harkawat" wrote in message
...
Hope this helps
=PRODUCT(IF(ISNUMBER(A2:A8),(1+A2:A8)))
array entered(ctrl+shift+enter) instead of just enter

"Stan Altshuller" wrote in message
...
Hi all

in short:
I have row A of data that has numbers between -1 and 1 and some
characters.
I need to find the value of Product(1+A:A). Using the function N to
convert
chars to nums has not done the trick.

more detail:

I get a data dump of return numbers in percent from a third party
software
that I must link to. Upon linking to a different spread sheet I use

=IF(DataIn!A1=0,"",DataIn!A1)

and drag this forumla to get rid of zeros in my linked data.

then I must find the total returns for all colums.
If I use Product(1+A1:A10) I get #Value since there are chars in the
data
(even though Its only "" it does not work.) I must use Product(1+randge)
not
simply product(range) since this is return data and I must add a 1 first
to
find the correct return.
Any ideas?
Thanks!
Stan