Rounding Problem

[sparlaman1]

HI All!

Problem: I want to "round" (to 2 decimal places) numbers 1-5 down and 6-9 up. For example:

1.915 = 1.91
1.916 = 1.92

I know this is completely screwy but I have to match numbers up to a purchasing system that seems to be doing just that.

I've researched rounding in Access a lot and I understand Bankers rounding (that won't work), I understand Int() and Fix() both don't do what I need. I've something about rounding half down (which is what I think I need) or Floor which I don't quite understand.

Any help anyone can provide or point me in the right direction I'll greatly appreciate.

Thank you in advance!

 

[Brianwarnock]

How about if you subtract 0.001 before rounding?

Brian

 

[Brianwarnock]

Actually I suspect that it is not quite as simple as that, you say that you have researched , have you read this
http://allenbrowne.com/round.html

And looked at Getz round function

Brian

 

[gemma-the-husky]

I had a brief play with div and mod and so forth, and it's really tricky. I imagine it's easy once you get the right technique ....

 

[Brianwarnock]

How about

Multiply by 100
Add 0.4
Int
Divide by 100


Brian

 

[sparlaman1]

Thank you all for your suggestions. It's funny how sometimes the simplest solution can be the hardest thing to come up with. So far with my test data the subtraction of .001 is working perfectly! I'm going to play around with it for a while but I think that's just what I needed.

 

[sparlaman1]

Ah... now I see the reasoning behind your second solution.. When I come across a number ending in .6666666 (etc.) and with an odd number to the left and bankers rounding I sometimes get .66 instead of .67 like I want. So if I do the *100 +.4 /100 it doesn't do that.

Still you are brilliant in my eyes for coming up with a simple solution!

 

[sparlaman1]

OK, so... the first answer of adding .001 is still working best except that now I need to get access to stop using bankers rounding because ulitimately I need to take this number to only 2 decimal places. So next I'm going to try using this plus Ken Getz' custom rounding function. I think that'll work...

 

[namliam]

Are you not going to have "issues" with the "real source" being:
1.9149 instead of 1.915??

1.9149 should rightfully round to 1.91...

 

[sparlaman1]

right. I want the answer to be 1.91. so the fourth digit is irrelevant in that example... except that access still uses bankers rounding so, I have a number like 5.666 and it's rounding to 5.66 instead of 5.67.

So as of right now the -.001 works and I just need to force access to use standard rounding (or whatever you call it) instead of bankers rounding

 

[sparlaman1]

well... i'm giving up on this. turns out the data that i'm working with is stored as type = currency and this data type rounds different from just a plain number. I am only linked to the data through odbc and not in control of the data type. so, I give up...

but thanks for all your suggestions and help...

 

[gemma-the-husky]

I can't see that currency/double is relevant. It's just an exercise in getting the right expression - which I am intrigued about, and will do later on today!

 

[sparlaman1]

You’re right it shouldn’t but when I take 2 occurrences of the same data set where one occurrence is set to currency and the other is set to number, then I perform the same calculation on each, I get two different numbers using only the round function. I’ll give you the setup in a moment with some of the numbers.

 

[Brianwarnock]

As I understood it you want to round to 2 decimal places with 1-5 down and 6-9 up. The solution in post 5 does that, and as it uses no Rounding function you do not need to worry about Bankers or anybody else.

The 1.9149
*100 191.49
Add .4= 191.89
Int 191
/100 = 1.91

Haven't got Access so tested it in Excel
Int((A1*100)+0.4)/100

Brian

 

[sparlaman1]

I missed the Int in that equation. No wonder I wasn't getting the same result.. I'll try again

 

[sparlaman1]

Wahoo!!!! thank you Brianwarnock. Now that I properly input your formula it is working perfectly!!!

(one of these days I'll learn to read properly)

Again, Thank you very much!