Rounding

[Jeff Clarke]

I have two currency fields. Call it currency1 and currency2, I'm attempting
to round the solution. For example currency1=$9,783; currency2=$15,550 The
straight answer is 62.9% ....I'm attempting to round it to 63.0. I've
attempted to used the INT function without success. I've attempted it
without vba code becuase its just a field in a report. I really just need
the value in a query. Can somebody show me the correct formula?

Thanks in advance,


Jeff

 

[Wayne Morgan]

There is a Round function in the newer versions of Access, but it does "scientific
rounding". It rounds 0.5 to the nearest even number instead of rounding up. I've also
heard that it may not be consistent about that.

The general formula for rounding is:


Int(NumberToRound * (10^-PlaceRoundingTo) + 0.5) / (10^-PlaceRoundingTo)

For example:
To round to the 100ths position (^-2):
Int(NumberToRound * (10^-(-2)) + 0.5) / (10^-(-2)) or
Int(NumberToRound * 100 + 0.5) /100

To round to the ones position (^0):
Int(NumberToRound * (10^0) + 0.5) / (10^0)
since 10^0 is 1, this factor drops out leaving
Int(NumberToRound + 0.5)

Here is a function that you can put in a module that should do what you want.

Public Function MyRound(NumberToRound As Single, PlaceToRound As Integer) As Single
TestRound = Int(NumberToRound * (10 ^ -(PlaceToRound)) + 0.5) / (10 ^ -(PlaceToRound))
End Function

Using your info below, you would call it as:
MyRound(PercentageNumber, 0)

The 0 is for 10^0 or the ones position.

Another possible problem to remember is that 62.9% is really 0.629. The decimal form is
the number that calculations will be done on, so you will actually want to round to the
100ths position, not the ones position.

 

[PAFS]

Jeff;

Trying to get a percentage (9783/15550) gives a decimal number 0.629 which
when displayed as a percntage gives 62.9%. The INT function will return 0
for any number less than 1! The following will return 0.63 which will
display 63% if displayed as a percentage: cint((9783/15550)*100)/100

Jim

 

[Wayne Morgan]

Oops, I forgot to change the name in the function from my test DB to the name I used in
the message.

The line "TestRound =" should read "MyRound ="

 

[Van T. Dinh]

(previously posted but never appeared in the newsgroup. Last paragraph is
still applicable)

If you use A2K+, use the Round function. For example:

?Round(0.629, 2)
0.63
You can use the Round() function in the Calculated Field of your Query or as
a Calculated Control on your Report.

BTW, it is mathematically incorrect to say 63.0% if you do the rounding this
way since the accuracy is not to the first decimal place after rounding. It
is more appropriate to simply say 63%.

 

[Wayne Morgan]

Van,

The "problem" with the Round function is that it does "scientific" or "banker" rounding.
At 1/2 it rounds to the nearest even number. While this is theoretically more accurate
(1/2 the time you round up and 1/2 the time you round down) and is the reason it is used
by the 2 above, most folks in the US will round up all the time at 1/2.

?Round(0.625, 2)
0.62
?Round(0.635, 2)
0.64

 

[Van T. Dinh]

That can be overcome easily with a tiny Bias that won't affect anything
except the exact 0.5. For example, if the values you are working on are up
to 3 decimal places, you can use Bias = 10 ^ -4. Add the Bias to round up
and subtract to round down. For example:

Bias = 10 ^ -4

?Round(0.625 + Bias, 2)
0.63

?Round(0.635 + Bias, 2)
0.64


?Round(0.625 - Bias, 2)
0.62

?Round(0.635 - Bias, 2)
0.63

The main point I was reposting is that saying 63% is correct but saying
63.0% is mathematically incorrect as the rounded number may not be accurate
to the first decimal place in percent format.

 

[Wayne Morgan]

Good idea. I hadn't thought about using the Bias, but it works fine.

 

[Mike Sherrill]

I've also
heard that it may not be consistent about that.

It's consistent. On Usenet, at least, you'll often find programmers
who *think* they've given it one number to round, when in fact they've
given it a slightly different number.