Solved Round result ??? (1 Viewer)

[smig]

How can it be ?


Round(me.Field1, 1) + Round(me.Field2, 1) = 100.00000375

 

[Galaxiom]

Binary is not good at storing precise factional values. The anomalies can pop out unexpectedly even when using Single or Double datatypes.

For precise arithmetic you can use the Decimal datatype which is stored as an integer with a scaling factor. Many developers use Currency which is a Decimal datatype with a fixed scaling factor that makes it precise to four decimal places.

 

[smig]

Binary is not good at storing precise factional values. The anomalies can pop out unexpectedly even when using Single or Double datatypes.

For precise arithmetic you can use the Decimal datatype which is stored as an integer with a scaling factor. Many developers use Currency which is a Decimal datatype with a fixed scaling factor that makes it precise to four decimal places.

Thanks

How can I convert from Single to Decimal without loosing data ?

 

[gemma-the-husky]

if you don't need more than 4 dps, you can use currency, as it's guaranteed.
Double is more accurate than Single.

You can't convert without losing data. That's the point. But you will only lose the small decimal expansions - It depends what you are doing as to whether these are significant to you.

 

[arnelgp]

Round(CDec(Me.Field1) + CDec(Me.Field2), 1)

 

[smig]

if you don't need more than 4 dps, you can use currency, as it's guaranteed.
Double is more accurate than Single.

You can't convert without losing data. That's the point. But you will only lose the small decimal expansions - It depends what you are doing as to whether these are significant to you.

Yes, Data is very impotent to me.

It seems that copying to another field before changing will work

 

[smig]

Round(CDec(Me.Field1) + CDec(Me.Field2), 1)

Can I use Cdec(Round(Me.Field1, 1) + Cdec(Round(Me.Field2, 1)
Will this give me the correct result that I need ?

I wand 2.33 + 4.54 to be 6.8 not 6.9 (Round of 6.87)

 

[gemma-the-husky]

I think you need to look at writing your own function with FIX() or maybe INT() rather than round.

round() does bankers rounding. Rounds up or down to the nearest number, and with a 5, round to the nearest even number.


maybe you need to check what happens

I want 2.33 + 4.54 to be 6.8 not 6.9 (Round of 6.87)

that example you gave probably would give you 6.8

what about 2.50 + 3.56 - what do you want that result to be. Does your expression give you the answer you want.

 

[smig]

I think you need to look at writing your own function with FIX() or maybe INT() rather than round.

round() does bankers rounding. Rounds up or down to the nearest number, and with a 5, round to the nearest even number.


maybe you need to check what happens



that example you gave probably would give you 6.8

what about 2.50 + 3.56 - what do you want that result to be. Does your expression give you the answer you want.

it should give me 6.1
2.50 should round to 2.5, and 3.56 should round to 3.6
This is what I know about math


but just remember where I started this discussion
Round(me.Field1, 1) + Round(me.Field2, 1) could give me 100.00000375
This makes no sense at all

I guess I can use Round(Round(me.Field1, 1) + Round(me.Field2, 1) , 1)

 

[gemma-the-husky]

How did you get the 100.00000375

IS that the result of a calculation?

.375 is 3/8, which doesn't look like a binary rounding error.

 

[The_Doc_Man]

Dave, with that many leading zeros, it is not 3/8.

@smig - the reason you get that wild result is because of factors.

Consider for a moment the proper fraction 1/3. When we express that as a decimal number, it is 0.33333...33333...to infinity and beyond. Why? Because 3 is NOT a factor of 10 at any time so the fraction never resolves. With 1/4 or 1/2 or 1/8, it takes no more than a small number of digits to resolve because 2 IS a factor of 10. And you can, in fact, predict how many digits. With 1/2, you have 0.5 which is 1 digit. With 1/4 (= 1/2 * 1/2) you get 0.25 (2 digits) because you had TWO twos. With 1/8 (= 1/2 * 1/2 * 1/2) you get 0.125, 3 digits because you had the factor 3 times. With 1/7 and 1/9, you get repeating fractions. For these 7 is most interesting because it is a pattern ... 0.142856142856..... ad infinitum.

So what does that have to do with you oddball fractional result? As it turns out, 1/10 is 1/2 * 1/5. A fraction of 1/2 resolves quickly in binary because 2 is a factor of the numerical base, 2. But a fraction of 1/5 cannot resolve because 5 is NOT a factor of 2. (Just as 3 is not a factor of 10). So what happens is you have something that is represented in binary but because fractions are involved - which were originally expressed as decimal fractions, that incommensurate base issue rears its ugly head. The fraction associated with your "6.1" could be expressed as 6 + (1/10), but if you express 1/10 in binary it is 0.0001100110011001100...11001100 ad infinitum. But you don't HAVE "ad infinitum" bits so you have to truncate. And when you do, you introduce a truncation error. When you then add numbers that contain truncated fractions, you suddenly have started to accumulate more errors. And expressing that result in decimal "trips" over the error.

The solution to your problem is to use either DECIMAL (scaled integer) or CURRENCY (also a scaled integer) where through scaling you avoid any actual decimal fractions.

 

[gemma-the-husky]

@The_Doc_Man

Yes, I know it's not 3/8, but it's a definite fraction of 3/8, (3/8 x 10^-5) and I can't see you would get that result with a binary digit error.
So I suspect it's actually a specific result of whatever calculation the OP undertook to get the figure in the first place. Maybe it came from a badly calculated spreadsheet. Hence the question about what the number actually represented, and where it originated.

 

[smig]

How did you get the 100.00000375

IS that the result of a calculation?

.375 is 3/8, which doesn't look like a binary rounding error.

Don't look at the specific numbers

the weird thing is that Round(Number) + Round(Number) could give a totally not rounded number like 100.0000xxx

 

[smig]

Dave, with that many leading zeros, it is not 3/8.

@smig - the reason you get that wild result is because of factors.

Consider for a moment the proper fraction 1/3. When we express that as a decimal number, it is 0.33333...33333...to infinity and beyond. Why? Because 3 is NOT a factor of 10 at any time so the fraction never resolves. With 1/4 or 1/2 or 1/8, it takes no more than a small number of digits to resolve because 2 IS a factor of 10. And you can, in fact, predict how many digits. With 1/2, you have 0.5 which is 1 digit. With 1/4 (= 1/2 * 1/2) you get 0.25 (2 digits) because you had TWO twos. With 1/8 (= 1/2 * 1/2 * 1/2) you get 0.125, 3 digits because you had the factor 3 times. With 1/7 and 1/9, you get repeating fractions. For these 7 is most interesting because it is a pattern ... 0.142856142856..... ad infinitum.

So what does that have to do with you oddball fractional result? As it turns out, 1/10 is 1/2 * 1/5. A fraction of 1/2 resolves quickly in binary because 2 is a factor of the numerical base, 2. But a fraction of 1/5 cannot resolve because 5 is NOT a factor of 2. (Just as 3 is not a factor of 10). So what happens is you have something that is represented in binary but because fractions are involved - which were originally expressed as decimal fractions, that incommensurate base issue rears its ugly head. The fraction associated with your "6.1" could be expressed as 6 + (1/10), but if you express 1/10 in binary it is 0.0001100110011001100...11001100 ad infinitum. But you don't HAVE "ad infinitum" bits so you have to truncate. And when you do, you introduce a truncation error. When you then add numbers that contain truncated fractions, you suddenly have started to accumulate more errors. And expressing that result in decimal "trips" over the error.

The solution to your problem is to use either DECIMAL (scaled integer) or CURRENCY (also a scaled integer) where through scaling you avoid any actual decimal fractions.

I don't understand how does it explain that Round(Number) + Round(Number) could give a totally not rounded number like 100.0000xxx

Won't it be simpler, in this case, to round the result again?
just to void this error ?
Round( Round(Number1) + Round(Number2) )

 

[gemma-the-husky]

aha!

Right. @smig put me on the right track.

round(a)+round(b)=c
let a and b be singles, and let c be a double

if a,b and c are all the same type, you don't get the issue.

So clearly single(a) is different to double(a), so somewhere along the line, there is a confusion in expressing the answer when mixing singles and doubles. At some point access math must go wrong trying to express each value, or the result in the same real number format.

Sub tryit()
Dim a As Single
Dim b As Single
Dim c As Double

a = 31.345
b = 28.126
c = Round(a, 1) + Round(b, 1)

MsgBox c

End Sub

 

[smig]

aha!

Right. @smig put me on the right track.

round(a)+round(b)=c
let a and b be singles, and let c be a double

if a,b and c are all the same type, you don't get the issue.

So clearly single(a) is different to double(a), so somewhere along the line, there is a confusion in expressing the answer when mixing singles and doubles. At some point access math must go wrong trying to express each value, or the result in the same real number format.

Sub tryit()
Dim a As Single
Dim b As Single
Dim c As Double

a = 31.345
b = 28.126
c = Round(a, 1) + Round(b, 1)

MsgBox c

End Sub

Ouch

As I had this error in a Conditional Formatting my guess it's a similar case.
In my case A and C are Sums of Singles.

Thanks
I think this case can be closed now.