a little math problem with Calc

alex_sato · July 5, 2012, 1:50am

Hi,

I have a problem with functions I made.

Let's say I create the function:

Function Test (n)
dim r(3)
r(0)=int(n)
r(1)=n-r(0)
r(2)=r(1)*100
r(3)=int(r(2))
Test = r
End Function

Then, in a empty spreadsheet take the following steps:
-select A1:D1
-press [F2]
-Insert the formula =TEST(E1)
-press [CTRL]+[SHIFT]+[ENTER]
-insert 11,18 in E1 and see the result in cell D1

I think the result should be 18 but I'm getting 17
Where is the error?

By the way, I downloaded and installed the latest version of LibreOffice
before write this email to make sure that is not a solved bug. And I tried
this at least in 3 computers.

Thanks in Advance

Alex Mitsio Sato

Villeroy · July 5, 2012, 9:43am

Am 05.07.2012 03:50, alex sato wrote:

Hi,

I have a problem with functions I made.

Let's say I create the function:

Function Test (n)
dim r(3)
r(0)=int(n)
r(1)=n-r(0)
r(2)=r(1)*100
r(3)=int(r(2))
Test = r
End Function

Then, in a empty spreadsheet take the following steps:
-select A1:D1
-press [F2]
-Insert the formula =TEST(E1)
-press [CTRL]+[SHIFT]+[ENTER]
-insert 11,18 in E1 and see the result in cell D1

I think the result should be 18 but I'm getting 17
Where is the error?

E1: input value
A1: =INT($E1)
B1: =$E1-$A1
C1: =$B1*100
D1: =INT($C1)

This is how it works in all spreadsheet applications. It applies to any other row where you copy the formulas to.
You may even save this simple calculation in xls(x) format and it will work in Excel.

Villeroy · July 5, 2012, 9:55am

Am 05.07.2012 11:43, Andreas Säger wrote:

E1: input value
A1: =INT($E1)
B1: =$E1-$A1
C1: =$B1*100
D1: =INT($C1)

This is how it works in all spreadsheet applications. It applies to any
other row where you copy the formulas to.
You may even save this simple calculation in xls(x) format and it will
work in Excel.

Stupid me!
That is in one formula:
=INT(($E1-INT($E1))*100)
or even
=MOD($E1;1)*100
Isn't it?

Steve_Edmonds · July 5, 2012, 8:37pm

Hi.
I tried your function but made the last line Test = r(3).
It does return 17.

My guess is that by the completion of r(2)=r(1)*100 the value is very slightly less than 18 and int then produces 17.

steve

Steve_Edmonds · July 5, 2012, 10:05pm

Hi.
In fact if I change your function
r(3)=(r(2)-18)*1000000000
Test = r(3)

the result is -2.8421709430404E-005
showing that 17 would be correct.

For these situations I often add my own tolerance to limit sensitivity
r(3)=int(r(2)+1e-9)

steve

Dennis_E_Hamilton · July 5, 2012, 10:43pm

What I found interesting was that Calc (in LO 3.3.2) did not have that problem:

Have A1 hold 11.18
Then A2 = INT(100*(A1-INT(A1)))

works just fine.

I think it would be safest to do

A3 = ROUND(100*(A1-INT(A1)))

though.

alex_sato · July 5, 2012, 11:00pm

There is no problem using formula in a cell to make the calculation. The
problem occurs only in a macro (using ooBasic). I used the Test macro just
to show my problem.
I don't know if it was expected or if it is a bug.
If it is not a bug I wish to understand why and what I could do to get what
I expect in the results.

Thanks

Alex Mitsio Sato

Regina · July 5, 2012, 11:28pm

Hi,

alex sato schrieb:

Hi,

I have a problem with functions I made.

Let's say I create the function:

Function Test (n)
dim r(3)
r(0)=int(n)
r(1)=n-r(0)
r(2)=r(1)*100
r(3)=int(r(2))
Test = r
End Function

Then, in a empty spreadsheet take the following steps:
-select A1:D1
-press [F2]
-Insert the formula =TEST(E1)
-press [CTRL]+[SHIFT]+[ENTER]
-insert 11,18 in E1 and see the result in cell D1

I think the result should be 18 but I'm getting 17
Where is the error?

It is the normal behavior of data type double. 11,18 has indefinite digits in dual system. So the stored value is cut somewhere. Because there are enough binary digits to round the dual number to 15 decimal digits, you do not notice it. But when you subtract the integral part, the others digits will shift left and new digits will be generated at the end, which does not represent the original value. The calculated difference becomes a little bit to small. After multiply with 100 you do not have 18,00 but 17,99... And that results in 17. The problem becomes more visible, when the integral part is larger.

You should not use Basic functions, but use Calc functions instead. They are often tweaked to avoid such problems. You can call Calc functions inside your Macro.

To get the first two decimal digits as integer value, it seems to be more accurate to use
r(3)=(n * 100) mod 100
in your macro.

Kind regards
Regina

alex_sato · July 6, 2012, 1:09am

Regina, thanks for your answer.

But can you tell me how to call Calc functions inside the macro?
I searched in docs and in the Google and don't found any clue.

By the way, just now I tought the possibility to rewrite math functions
using "string" to encode the number to avoid error because I remembered
BCD.

Regards

Alex Mitsio Sato

Regina · July 6, 2012, 1:13pm

Hi Alex,

alex sato schrieb:

Regina, thanks for your answer.

But can you tell me how to call Calc functions inside the macro?
I searched in docs and in the Google and don't found any clue.

It is done with the service FunctionAccess, see http://api.libreoffice.org/docs/common/ref/com/sun/star/sheet/FunctionAccess.html and the there linked section in the Developers guide.

Here a very simple example, how it looks in Basic

function MYPRODUCT (byVal x as double,byVal y as double) as double
dim oFunction as variant
oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
dim aArgument(1) as variant
dim result as double
aArgument(0)=x
aArgument(1)=y
result = oFunction.callFunction( "PRODUCT", aArgument() )
MYPRODUCT = result
end function

If you search for createUnoService("com.sun.star.sheet.FunctionAccess") you will get a lot of relevant hits.

By the way, just now I tought the possibility to rewrite math functions
using "string" to encode the number to avoid error because I remembered
BCD.

What is your goal? There is no silver bullet that fits all problems, but perhaps you can get a helpful tip or trick.

Kind regards
Regina

alex_sato · July 6, 2012, 10:37pm

Regina.

I'm writing this email just to say "thank you" again.

About the idea of rewriting math function using string. I may do it but
just for fun, not for a serious use.

Regards

Alex Mitsio Sato

Villeroy · July 7, 2012, 8:08am

Am 07.07.2012 00:37, alex sato wrote:

Regina.

I'm writing this email just to say "thank you" again.

About the idea of rewriting math function using string. I may do it but
just for fun, not for a serious use.

Programming Basic is no fun. Never.

Function Test (n)
dim r(3)
r(0)=CINT(n)
r(1)=n-r(0)
r(2)=r(1)*100
r(3)=CINT(r(2))
Test = r
End Function