Ask Your Question
0

Problem with NUMBERVALUE() and WEBSERVICE() functions

asked 2017-10-19 00:43:43 +0200

Oikif gravatar image

updated 2017-10-19 01:56:16 +0200

When I enter the following formula =NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=" & A2 & "&f=l1")) I'm getting the #VALUE!(Error:Wrong value type). A2 is cell A2. If I remove the NUMBERVALUE I'm getting the right number! I need to convert the return from WEBSERVICE function into a number so I can perform some calculations. If I use the return value from WEBSERVICE I'm getting the #VALUE!. Is there any other way to convert to number the result returned by WEBSERVICE? I'm using v. 5.4.2.2.

Thank you

edit retag flag offensive close merge delete

Comments

The number displayed, for example, is 19.41. It has just digits and decimal point. To replicate the error enter IBM in A1 and =NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=" & A2 & "&f=l1")) in A2. if you remove NUMBERVALUE you get 159.53, if you add NUMBERVALUE you get #VALUE!

Oikif gravatar imageOikif ( 2017-10-19 02:44:54 +0200 )edit

Check to make sure there isn't a trailing space.

robleyd gravatar imagerobleyd ( 2017-10-19 03:34:58 +0200 )edit

Please use comments not answers for comment.

m.a.riosv gravatar imagem.a.riosv ( 2017-10-19 10:50:21 +0200 )edit

one can convert an answer to a comment ;)

cornouws gravatar imagecornouws ( 2017-10-19 10:52:22 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2017-10-19 02:27:32 +0200

m.a.riosv gravatar image

updated 2017-10-19 10:54:37 +0200

Works for me on the Webservice_Filterxml_functions_test.ods, in this case using FILTERXML() to extract data from WEBSERVICE.

Are you sure the result of WEBSERVICE it's a number as text with no other characters in.

Edited 20171019

NUMBERVALUE() has three parameters, the second one indicating decimal separator, if your locale use "," (comma) you must specify it as "." (dot)

image description

edit flag offensive delete link more

Comments

Yes, it worked. Thank you. I thought the decimal separator defaults to local settings.

Oikif gravatar imageOikif ( 2017-10-20 00:19:51 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-19 00:43:43 +0200

Seen: 673 times

Last updated: Oct 19 '17