Page 1 of 1

VAL function problem

Posted: Thu Dec 10, 2009 5:20 pm
by mrjaybov
I have a string that equals 9889.98 when I use the VAL function to convert it to a number I get 9889.979999999 Why is that? I'm converting lots of numbers this way and I always get one or two that does this.. It's throwing my totals off.

Posted: Thu Dec 10, 2009 7:05 pm
by burger2227
I can't duplicate the error. Try printing the string and VAL return.

Are you using variables like: value = VAL(number$) ?

Undefined numerical variables default as Single, but it seems like a Double problem.

Posted: Thu Dec 10, 2009 10:15 pm
by mrjaybov
I am pulling the number from a comma delimited file by using the MID$ command, AMT$ = MID$(data$,2,7) then all I do is add it to a totals variable like this: Total# = Total# + VAL(AMT$). Funny thing is, it only happens once in a while, there's no rhyme or reason. When I print out the string AMT$ it equals 9889.89, once it convert it using VAL(AMT$) and print I get 9889.8899999. Could it be a variable issue?

Posted: Thu Dec 10, 2009 10:56 pm
by Mentat
Most likely due to the fact that there's no possible 32 bit pattern for 9889.89, so it settles with the closest that it can find: 9889.8899999. Some decimal numbers (like 0.1 IIRC) are repeating decimals in binary, so it would take an infinite amount of bits to be right. Since that's not possible (we use 32 bits or 64 bits for floating point numbers), the computer approximates to the nearest possible number it can find. My guess is that this is one of those numbers.

I see what happened...

Posted: Fri Dec 11, 2009 12:07 am
by burger2227
You are using Double variables which creates the longer value accuracy.

num$ = "9889.98"

value = VAL(num$) ' Single value is accurate

TOTAL# = TOTAL# + value ' returns TOTAL as a Double value


Certain values, not all, will do that. If you are adding a lot of values, you can convert the results back to a string to get rid of the extra decimal places.

Or you can display the trimmed results with:

PRINT USING " #############,.##"; TOTAL#

PRINT USING will also round the decimal places, but that is just for show. The actual value does not change.

Posted: Fri Dec 11, 2009 12:27 am
by mrjaybov
You are correct. I changed my code and it corrected my VAL issue. But that brought about another problem, when I do the
TOTAL# = TOTAL# + value ---- it returns TOTAL as a Double value, but brings me right back to my original problem that I had. 9889.89 turns into 9889.8896484375 for some reason...

My total will be in the millions with a decimal, do I need to define it as a Double?

Crap.. this can't be this hard!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
All I want to add is add large numbers together....

Posted: Fri Dec 11, 2009 1:33 am
by burger2227
You can use Double values. When the total is done, use a string. Single values may not work if they are over 7 digit numbers:

sum$ = LTRIM$(STR$(TOTAL#))

'Use INSTR to find the decimal point place:

position% = INSTR(sum$, ".") + 2 ' for 2 decimal places

result$ = MID$(sum$, 1, position%)

PRINT result$