Expression returns wrong answer

[Jean-P. B�dard]

I have two fields in a database

Balance N,10,3
Result N,11,2

Result is a calculated field with following expression:
(INT(IF(Balance

[Stan Mathews]

RE: Expression returns wrong answer

JP,

Interesting puzzler. I can verify your experience. Just for giggles I tried

(INT(IF(Balance

[Jean-P. B�dard]

RE: Expression returns wrong answer

Thanks Stan,

I will modify my expression and change 100 for 1000 and see if it works.

Thanks again

JP

[whanigsberg]

RE: Expression returns wrong answer

Isn't the Round function designed for this:
Result=If(balance

[Stan Mathews]

RE: Expression returns wrong answer

JP,

I don't think using 1000 will solve your problem. When you have a number such as -39.159, you will not get the desired result. It just happens that the 1000 works for numbers with only two decimal places.

Maybe if you tested for the decimal point. Stored the digits after the decimal to a variable, tested the variable for =100 (use the expression with 1000)

Stan

[Stan Mathews]

RE: Expression returns wrong answer

JP,

Ira semms smarter everytime I remember his advice.

Here's a function to truncate your number. You can modify the "str(input,12,4)" to accomodate more digits and decimal places as you wish, just change the second parameter in the "left()" to two less than the second parameter in the "str()" function
.
'************************************************
function truncate as N(input as N)
'check for negative value, end if 0 or positive
if input>=0
truncate=0
END
end function
end if

' Pass the number to the function

trans1 = str(input,12,4) 'change to string, known decimal length
trans2 = left(trans1,10) 'strip off last two decimal places
' Return value
truncate = val(trans2) 'change back to number


END
end function


Stan

[Stan Mathews]

RE: Expression returns wrong answer

JP,

If it's not obvious to you, the syntax would be

truncate(balance).

Stan

[Jean-P. B�dard]

RE: Expression returns wrong answer

Thanks to all,

After a second check only two decimal place numbers are entered, so the 1000 should work, but the solution from Ira looks much better and I will also try Bill method.

Thanks again it is really appreciated, I knew I could rely on the board to solve my problem....as usual.

Best regards to all.

JP

[Jean-P. B�dard]

RE: Expression returns wrong answer

I meant numbers have never more than 3 decimal places so the "1000" may not work.

I will try Ira method and let you know.

The round method does not seem to work. -39.156 will return -39.16.Any third decimal place with higher than 5 will return the next rounded number.

Thanks again

JP

[csda1]

RE: Expression returns wrong answer

JP,

When you use the round method, subtract -.005 for the 3rd decimal place as in

round(value-.005,2)

I was able to duplicate your problem, which has to do with the constant conversion of -39.160 to the variable storage in balance.

However, after playing with a variety of numbers and equations, I found the problem went away. This implies that Alpha (or is it windows/numeric processor) has some small fractional value that is not directly settable (maybe down in the 10th digit for example) that is not necessarily cleared or set appropriately all the time. This worries me a little bit.

I am not sure if string conversion back & forth will not have consequences as well.

Finally it may be possible todo something with checking the bounds of the value to be near a value.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Jean-P. B�dard]

RE: Expression returns wrong answer

Ira,

Many thanks for your help.
What bugs me also is that when I duplicate the expression in a Quattro spreadsheet, I do not get that error.

Since that special application is just used by a very few peoples, we can get around it without major problems. It is just annoying, and we get that error with very few entries and we do not see any specific pattern on how it happens.

Thanks again and regards

JP

[Stan Mathews]

RE: Expression returns wrong answer

From the interactive window:

test=-35.160

? test

[Jean-P. B�dard]

RE: Expression returns wrong answer

Here is a test file I prepare with quite a few numbers.

Column 1: Entered value
Column 2: Result using INT(value*100)/100
Column 3: Value * 100
Column 4: INT(column 4 result)
Column 5: (Column 5 result)/100

Rows in bold do not return same result with both metods

Bizarre ?

JP

I hope file is there

[Jean-P. B�dard]

RE: Expression returns wrong answer

Forgot to say that you should look at the browse to see results.

Thanks

JP