Number Format

[Guest]

I have a text box that is a DSum of all the weighted scores a student has
achieved for each assignment. The assignment weighted score is of number type
single formatted with 2 decimal places.

The scores are computed based on a certain weightage assigned. For example
in assignment 1, the weightage is 20% and the score given is 91. The weighted
score works out to be 18.2 (91 * 20/100). For assignment 2, the score is 67,
and the weighted score is 13.4 and finally for assignment 3, the score is 99,
with the weighted score computed to be 19.8.

What I'm puzzled is that the total weighted score should be 18.2 + 13.4 +
19.8 = 51.4. However, the text box with the DSum function added the total to
be 51.3999996185303. Any ideas why? Thanks.
ck

 

[Guest]

Hi, CK.

Formatting a numeric control simply changes how the number is displayed, not
how the value is stored internally. To convert each to exact decimal, you
can multiply the number by the appropriate power of 10 (the number of decimal
places you'd like to retain), round it to the appropriate integer, and then
divide it back.

For example, to convert 10.828723 to 10.83,

=Round(10.828723*100)/100

Hope that helps.
Sprinks

 

[Tom Ross]

Your numbers are single precision floating point numbers. There is often a
small error
that comes with those kind of numbers (called floating point error). Use
integer type (with 1 decimal place) to bypass this error. Your numbers
should all fall within the range of these numbers

but-------------

Scientifically 51.4 if very close to 51.39999961....

It only makes a difference if your grading system is accurate to 7 decimal
places (9 significant figures)

Your test results are 2 significant figures (91 not 91.00000061 or
90.9999972) multiplied by 20% (also two significant figures) Your resultant
computation is only accurate to 2 significant figures (eg 51 not 51.4).
Looking at 1 decimal place is already streching the accuracy of the
numbers.

Most tests and exams have grading systems that are accurate to the nearest
integer or 1 decimal place. (at the very most) We cannot measure
learning, knowledge or intelligence much more accurately than that. Ignore
those decimal points; your measurements are not that accurate. (would you
say a student with 51.4 is significantly more knowledgeable than one with
51.39999961?)

This is my pet peeve as a scientist. because we can calculate to many
decimal places we do. But seientific rules tell us that those decimal
places are not significant.

Tom

 

[fredg]

Your numbers are single precision floating point numbers. There is often a
small error
that comes with those kind of numbers (called floating point error). Use
integer type (with 1 decimal place) to bypass this error. Your numbers
should all fall within the range of these numbers

*** snipped ***
Regarding > Use integer type (with 1 decimal place) to bypass this
error. <

By definition, an Integer is a whole number and cannot have a
decimal/fractional value.

The OP should round his answer, as suggested, or change the fields
datatypes used in the calculations to Currency datatype to avoid
rounding errors.

 

[Tom Ross]

Currency type! Duh would General Number also work? Or is that also a
floating point type number?

 

[fredg]

Currency type! Duh would General Number also work? Or is that also a
floating point type number?

You're confusing the field's Format property with it's Datatype
property
There is no General Number datatype.
There is a General Number Format.

If, in Table design View, you click on a field's datatype property
dropdown, you will see, among others, AutoNumber, Number, Currency,
Text, etc,. Those are the available datatypes.

If you select Number datatype and then click on the Field Size
dropdown (lower panel), you will see the available Size properties,
i.e. Single Integer, Double, etc. to go with the number datatype.

If you then click on the field's Format property dropdown, you will
see a list of the available formats for the type of datatype you
selected. Among other formats are General Number and Currency. But
here the Currency format is way the field displays, i.e $123.24, not
the way the field data is actually stored.
I hope this has helped.

 

[Guest]

Thanks, Sprinks for that tip. What I don't understand is that the following
scores have been computed such that it has only 1 decimal place:

Assignment 1 = 91. Weighted score = 91 * 20/100 = 18.2
Assignment 2 = 67. Weighted score = 67 * 20/100 = 13.4
Assignment 3 = 99. Weighted score = 99 * 20/100 = 19.8

As can be seen from the weighted score of assignment 1, they are exactly
18.2 and not 18.2134567 or something like that. The same goes for the others.
So the DSum result should be 18.2 + 13.4 + 19.8 = 51.4 exactly.

The funny thing is that the textbox shows up as 15.4 correctly but when I
click on the field itself, that's when I see the 51.3999996185303. I have a
problem because I have an action query that will update the score in the
textbox into another table and it updates into that table as 51.4. In
addition, I compare this textbox with the field in that table to see if they
are equal and if they are not, it will pop up a dialog box. So you can see
that the dialog box WILL keep popping up because the textbox is
51.3999996185303 while the field in that table is 51.4. Thanks.
ck

 

[Guest]

Thanks Tom. The problem I face is not whether which is more accurate but
because I have a code that updates the value into another table and it
performs the check to see if the value has changed and a dialog box will pop
up if they are not equal. The text box with the DSUm displays correctly as
51.4 but when I click into the field itself, I see 51.3999996185303. When the
value is updated to the table, it updates as 51.4. So the dialog box will
keep popping up because the value in the table that has been updated is 51.4
but the value in the textbox is 51.3999996185303.
ck

 

[Tom Ross]

Looks like you have to use the rounding function or use currency format.
Currency format does not have rounding errors (that would cause accounts to
have siezures). You can have your forms and reports display it without the
$

 

[Brendan Reynolds]

If you want the comparison to ignore very small differences, you can do
something like ...

Public Function CloseEnough(ByVal Value1 As Double, ByVal Value2 As Double)
As Boolean

If Abs(Value1 - Value2) <= 0.01 Then
CloseEnough = True
End If

End Function

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Thanks Brendan, Tom, fredg and Sprinks. I've adopted the suggestion from
Sprinks and it seemed to work ok. I just needed to create another which
stores that 51.3999996185303 value and then perform the round function and
store that rounded value to 2 decimal places into another field. Thanks for
all your helpful ideas again.
ck