[Solved] Strange mathematic in LibreOffice - Writer

[Ponny]

Meow

I don't know how exactly to explain this by words. Is it realy strange.
And I'm not sure, if this problem belongs in this subforum.

Well, LibreOffice Basic has very strange mathematic. For example, there is a little macro:

Code: Select all

Sub Main
Dim a As Single
Dim b As Single
Dim c As Single

a = 1.8
b = 1.7
c = a - b
d = Msgbox (c, 0, "Ponny")

End Sub

The result returnet in MsgBox is 9,99999E-02.
Correct result is 0,1.
But I found this type strange result in more situations. But I tested only in some cases betwen 1 and 23. What I doing wrong?

This test is made only in Windows version of LiBo.

Ponny

[Zizi64]

This difference is due to computational inaccuracies
0,1 approximately equal to 0,0999999 = 9,99999E-2

Use double precision variables for greater accuracy, and use rounding and formatting methodes to display the result.

[Ponny]

Hoy

I tryed your recipe and result is 0,1. Problem virtually solved.
But then I tried this:

Sub Main
Dim a As Double
Dim b As Double
Dim c As Double

a = 23.9
b = 23.8
c = a - b
d = Msgbox (c, 0, "Poni")

And result is 9,99999999999979E-02
Right result sholud be 0,1 of horse.

What now?

Aha, and why rounding, if input data already use only one or two decimals?
By the way, is there in LibreOffice Basic any function for rounding?

Ponny

[Zizi64]

What now?

1.: The result is very, very close to the ideal result. Use the result for next calculations.

2.: Format the result to desired layout.

Code: Select all

Sub double_example
Dim a As Double
Dim b As Double
Dim c As Double

a = 1.8
b = 1.7
c = a - b
d = Msgbox (Format(c, "0.0"), 0, "Ponny")

End Sub

[Ponny]

Just one question.
Is there any rounding function that returns a numeric value?

Ponny

[Zizi64]

Note:
The value 0,1 (decimal) have infinite fraction in the binary system:
0,1 (dec) = 1/2+1/4+1/8+1/16+1/32+1/64+1/128+1/256+1/512+ ... + 1/33554432
= 0,999999970197678 (dec)
(like the 1/3 = 0,33333333333... in the decimal system)

You can round or truncate this infinite fraction value.
You can use the FORMAT() function, the FIX() function, the INT() function.

BAsed on this functions, You can write a custom ROUND() function. For example:

Code: Select all

Function Round(num,dec)
   Round = Int(num * 10 ^ dec + 0.5) / 10 ^ dec
End Function

see:
http://www.oooforum.org/forum/viewtopic.phtml?t=75251

[Ponny]

Thankyou

But 0,1 is definitely not equal 0,999999970197678

Well, I will declare this problem as solved

Thankyou again

[Zizi64]

But 0,1 is definitely not equal 0,999999970197678

It was an example only, I have summarized "reciprocal values of the powers of the two" from 1/2^1 to 1/2^25...
Please continue it, because I can NOT type in the INFINITY...

[Ponny]

What type of data uses this private function? I mean second variable named dec.
I tried number 2 for rounding on two decimals. But no effect. No rounding.

I made this test:

Code: Select all

sub Main
Dim a As Single
Dim b As Single
Dim c As Single

a = 23.08
b = 23.09
c = a - b
c = Round(c, 2)
msgbox c
end sub

Function Round(num, dec)
   Round = Int(num * 10 ^ dec + 0.5) / 10 ^ dec
End Function

Result in variable c and msgbox is: -1E-02

[Zizi64]

What type of data uses this private function?

The result is single, or double presision floating point. The the division operation will return with a floating point value. But you can force the preferred type:

Code: Select all

Function Round(num as double, dec as integer) as double

Note: The rounding procedure and the formatting procedure are not same...

You need use the Format() function (after rounding or instead of rounding) to force the "0.00" format.

Code: Select all

msgbox Format(c, "0.00")

[Ponny]

Well, just one last example. It is very funny.

If I put value 0,01 in variable Double with command:
(in europe we use sign decimal point (,) as decimal separator)

a = 0.01

Then in bottom window for following variables I see
a = 1E-02

But this is just setting variable, without any treatment / processing.

Anyway, thank you for answers

Ponny

[Zizi64]

(in europe we use sign decimal point (,) as decimal separator)

Yes, I know that. Hungary is in Europe...

(I am living in Budapest, as you can see in my signature)

Then in bottom window for following variables I see
a = 1E-02

Where is it? Do you see it in the Basic IDE? In the "variable watch window"?

a = 0.01 (StarBasic variable input format) = 0,01 (European decimal format) = 1*10^(-2) (scientific format) = 1E-2 (scientific format for computers)

The Format() function will return with a string, but not a number.
The user defined Round() function will return with a numeric value. (But you can embed the Format() function into an another custom function:

Code: Select all

sub Main
Dim a As Double
Dim b As Double
Dim c As Double
Dim s as string

a = 1000
b = 234
c = a - b

s = Round_and_Format(c, 2) 
'try it with 0 and negative values too...

msgbox s

end sub

Function Round_and_Format(num as double, dec as integer) as string
Dim FormatCodeStr as string
Dim Rounded As Double  
Dim i as integer  
   
   FormatCodeStr = "0"
   Rounded = Int(num * 10 ^ dec + 0.5) / 10 ^ dec
   if dec > 0 then
   		FormatCodeStr = FormatCodeStr + "."
   		for i = 1 to dec
   			FormatCodeStr = FormatCodeStr + "0"
   		next i
   endif
   Round_and_Format = Format(Rounded, FormatCodeStr)	
End Function