Accuracy

J

John Porcella

A colleague was asking me how to use MS Access to round numbers to the
nearest 1/500th. This means that numbers must be rounded (God knows why!)
to 0.002, 0.004, 0.006, 0.008 or 0.000. So for example, 1.23456 would be
converted to 1.234, and 6.25012 would be shown as 6.260.

Ideally, no VBA or SQL should be used.

I have come up with a handwritten algorithm which (I think) will do the
trick. However, I do not know how to implement this in MS Access.

The problem is to display answers to some calculation or other to the
accuracy as mentioned above.

I can tidy up my written algorithm and post it here if it helps, but I do
not want to influence your thinking. My algorithm looks slightly involved
and cumbersome and I was hoping that there was an easier way!

On a slightly related issue, can numbers be truncated in MS Access? For
example, if I have the number 1.23456 and I want this truncated to three
decimal places (i.e. to 1.234, not rounded!!), is there a command to do
this?

Thanks in advance to all, and if it would help to post my little algorithm,
then just tell me.
 
M

Mike Painter

John said:
A colleague was asking me how to use MS Access to round numbers to the
nearest 1/500th. This means that numbers must be rounded (God knows
why!) to 0.002, 0.004, 0.006, 0.008 or 0.000. So for example,
1.23456 would be converted to 1.234, and 6.25012 would be shown as
6.260.

Ideally, no VBA or SQL should be used.
Click to expand...

It is not going to get done without VBA.
I have come up with a handwritten algorithm which (I think) will do
the trick. However, I do not know how to implement this in MS Access.

The problem is to display answers to some calculation or other to the
accuracy as mentioned above.

I can tidy up my written algorithm and post it here if it helps, but
I do not want to influence your thinking. My algorithm looks
slightly involved and cumbersome and I was hoping that there was an
easier way!

On a slightly related issue, can numbers be truncated in MS Access?
For example, if I have the number 1.23456 and I want this truncated
to three decimal places (i.e. to 1.234, not rounded!!), is there a
command to do this?
Click to expand...

It can be done but moving from binary to decimal will frequently cause
problems and additionally Access uses bankers rounding so it would round the
5 in your number up about half the time and down the other half. This is
usually more precise and never less precise than always rounding the same
way on a "5"


What with the speed of computers today I keep waiting for Intel to re-invent
BCD.
 
L

LGC

A colleague was asking me how to use MS Access to round numbers to the
nearest 1/500th. This means that numbers must be rounded (God knows why!)
to 0.002, 0.004, 0.006, 0.008 or 0.000. So for example, 1.23456 would be
converted to 1.234, and 6.25012 would be shown as 6.260.

Ideally, no VBA or SQL should be used.

I have come up with a handwritten algorithm which (I think) will do the
trick. However, I do not know how to implement this in MS Access.

The problem is to display answers to some calculation or other to the
accuracy as mentioned above.

I can tidy up my written algorithm and post it here if it helps, but I do
not want to influence your thinking. My algorithm looks slightly involved
and cumbersome and I was hoping that there was an easier way!

On a slightly related issue, can numbers be truncated in MS Access? For
example, if I have the number 1.23456 and I want this truncated to three
decimal places (i.e. to 1.234, not rounded!!), is there a command to do
this?

Thanks in advance to all, and if it would help to post my little algorithm,
then just tell me.
Click to expand...

You'll have to use VBA to accomplish your task. Here's a 'quick & dirty'
solution (Not fully tested):

Public Function fdRound500(dNum As Double)

Dim d As Double
Dim dInt As Double
Dim dFraction As Double


d = dNum * 100
dInt = Int(d)
dFraction = Round(d - dInt , 3)

Select Case dFraction
Case Is < 0.1
fdRound500 = dInt / 100
Case Is < 0.3
fdRound500 = (dInt + 0.2) / 100
Case Is < 0.5
fdRound500 = (dInt + 0.4) / 100
Case Is < 0.7
fdRound500 = (dInt + 0.6) / 100
Case Is < 0.9
fdRound500 = (dInt + 0.8) / 100
Case Else
fdRound500 = (dInt + 1) / 100
End Select

End Function


You can place this function in a module and call it as needed.

The INT function will truncate numbers. To truncate to the third decimal
place:
Number = Int(Number * 1000)/1000

Hope this helps,

LGC
 
J

John Porcella

LGC,

I am going to look at what you have presented on the way to work. I have
typed up my algorithm to achieve the desired result. If it is correct, then
does this make you think of any other way(s)?

Incidentally, how do you call up your function in Access? Also, how do you
get your function to take on board the number to be rounded?

********************************************

I think that this will work in rounding numbers to the nearest 1/500th (i.e.
0.000, 0.002, 0.004, 0.006 or 0.008).


Step 1. Let N be any number

Step 2. Let A = N x 1,000 Remark: To multiply the number by 1,000

Step 3. Truncate off the decimal places of A, or show A with zero decimal
places Remark: I think that either will work. If the latter rounds up/down
correctly, then it might be better!

Step 4. Let B = A Remark: Not really sure this was needed

Step 5. If (B/2) is an even number i.e. no decimal values, then Solution =
(B/1,000), Else Solution = ((B+1)/1,000)

Test:

(a) For N = 0.003000 Remark: We can see that this should be rounded up to
0.004, to the nearest 1/500th.

Step 2. A = 3.000

Step 3. A = 3

Step 4. B = 3

Step 5. 3/2 is not an even number, so Solution = (3 +1)/1,000 = 0.004

Test:

(b) For N = 0.003999 Remark: We can see that this should be rounded up to
0.004, to the nearest 1/500th.

Step 2. A = 3.999

Step 3. A = 3, if truncated, [or A = 4 if rounded to zero decimal places]

Step 4. B = 3, [or B = 4]

Step 5. For B = 3, you get the same solution as in the test before i.e.
0.004, which is what we should expect.
.. [ For B = 4, since 4/2 is an even number, so Solution = (4/1,000) = 0.004]

Thanks for your input so far.


--
MESSAGE ENDS.
John Porcella
 
L

LGC

Incidentally, how do you call up your function in Access? Also, how do you
get your function to take on board the number to be rounded?
Click to expand...

It depends on where you need to round the number. It can be used in a query
or a calculated control.

=fdRound500( [Number])
I think that this will work in rounding numbers to the nearest 1/500th (i.e.
0.000, 0.002, 0.004, 0.006 or 0.008).


Step 1. Let N be any number

Step 2. Let A = N x 1,000 Remark: To multiply the number by 1,000

Step 3. Truncate off the decimal places of A, or show A with zero decimal
places Remark: I think that either will work. If the latter rounds up/down
correctly, then it might be better!

Step 4. Let B = A Remark: Not really sure this was needed

Step 5. If (B/2) is an even number i.e. no decimal values, then Solution =
(B/1,000), Else Solution = ((B+1)/1,000)

Test:

(a) For N = 0.003000 Remark: We can see that this should be rounded up to
0.004, to the nearest 1/500th.

Step 2. A = 3.000

Step 3. A = 3

Step 4. B = 3

Step 5. 3/2 is not an even number, so Solution = (3 +1)/1,000 = 0.004

Test:

(b) For N = 0.003999 Remark: We can see that this should be rounded up to
0.004, to the nearest 1/500th.

Step 2. A = 3.999

Step 3. A = 3, if truncated, [or A = 4 if rounded to zero decimal places]

Step 4. B = 3, [or B = 4]

Step 5. For B = 3, you get the same solution as in the test before i.e.
0.004, which is what we should expect.
. [ For B = 4, since 4/2 is an even number, so Solution = (4/1,000) = 0.004]

Thanks for your input so far.
Click to expand...

Your algorithm is much more efficient:

Public Function fdRound500(dNum As Double) As Double

dNum = Int(dNum * 1000)
If (dNum And 1) = 0 Then
fdRound500 = dNum / 1000
Else
fdRound500 = (dNum + 1) / 1000
End If

End Function
 
Top