By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,806 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Rounding Issues - HELP!

P: n/a
Sorry in advance for the lack of formatting in this posting.

Data:

(column headers)
Net Sales | Royalty Rate | Total Royalty
(data)
4.31 | 50.00% | 2.15
19.35 | 50.00% | 9.68

What gives?

Here is the query that runs this calculation:

UPDATE
[WT_Royalties-detail]
SET
[WT_Royalties-detail].[Total Royalty] = Round([Net Sales]*[Royalty
Rate],2)
WHERE
((([WT_Royalties-detail].Ref)="LSI"));

So why is the first row rounding down while the second row is rounding
up?

Note: Before I added the Round() function, the rows returned 2.155 and
9.675 respectively, if that helps.

Thanks!

Oct 11 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"Cygnus" <sa*@firstbooks.com???????/???????? ? ???????? ?????????:
news:11*********************@h48g2000cwc.googlegro ups.com...
So why is the first row rounding down while the second row is rounding
up?
That must be the "banker's rounding" issue (not a crime).

You could use something like

Int([Net Sales]*[Royalty Rate]*100)/100

instead of Round(.., 2).
Oct 11 '06 #2

P: n/a

Eman wrote:
"Cygnus" <sa*@firstbooks.com???????/???????? ? ???????? ?????????:
news:11*********************@h48g2000cwc.googlegro ups.com...
So why is the first row rounding down while the second row is rounding
up?

That must be the "banker's rounding" issue (not a crime).

You could use something like

Int([Net Sales]*[Royalty Rate]*100)/100

instead of Round(.., 2).
Unfortunately, this will not work and should not be used by someone
looking to ROUND.

Here's why.

[Net Sales]*[Royalty Rate]*100 = 2155
Int([Net Sales]*[Royalty Rate]*100) = 215.
Int([Net Sales]*[Royalty Rate]*100)/100 = 2.15

2.155 needs to be rounded to 2.16, not 2.15. All this line of code does
is truncate and that is not what we need to do. We need to round.

Thanks, though.

Oct 12 '06 #3

P: n/a
"Cygnus" <sa*@firstbooks.comwrote in
news:11**********************@c28g2000cwb.googlegr oups.com:
>
Eman wrote:
>"Cygnus" <sa*@firstbooks.com???????/???????? ? ???????? ?????????:
news:11*********************@h48g2000cwc.googlegr oups.com...
So why is the first row rounding down while the second row is
rounding up?

That must be the "banker's rounding" issue (not a crime).

You could use something like

Int([Net Sales]*[Royalty Rate]*100)/100

instead of Round(.., 2).

Unfortunately, this will not work and should not be used by someone
looking to ROUND.

Here's why.

[Net Sales]*[Royalty Rate]*100 = 2155
Int([Net Sales]*[Royalty Rate]*100) = 215.
Int([Net Sales]*[Royalty Rate]*100)/100 = 2.15

2.155 needs to be rounded to 2.16, not 2.15. All this line of code
does is truncate and that is not what we need to do. We need to round.

Thanks, though.
There are many VB rounding functions to be found at
http://www.xbeat.net/vbspeed/c_Round.htm
I think all of them will work in VBA.
Perhaps, you can find one that will fit your needs exactly there.

--
Lyle Fairfield
Oct 12 '06 #4

P: n/a
"Cygnus" <sa*@firstbooks.com???????/???????? ? ???????? ?????????:
news:11**********************@c28g2000cwb.googlegr oups.com...
>
Eman wrote:
>"Cygnus" <sa*@firstbooks.com???????/???????? ? ???????? ?????????:
news:11*********************@h48g2000cwc.googlegr oups.com...
So why is the first row rounding down while the second row is rounding
up?

That must be the "banker's rounding" issue (not a crime).

You could use something like

Int([Net Sales]*[Royalty Rate]*100)/100

instead of Round(.., 2).

Unfortunately, this will not work and should not be used by someone
looking to ROUND.

Here's why.

[Net Sales]*[Royalty Rate]*100 = 2155
Int([Net Sales]*[Royalty Rate]*100) = 215.
Int([Net Sales]*[Royalty Rate]*100)/100 = 2.15

2.155 needs to be rounded to 2.16, not 2.15. All this line of code does
is truncate and that is not what we need to do. We need to round.
Yeah, i've wrote "something like" ;)
Int(x + 0.5) will provide habitual "arithmetical" rounding.

Oct 12 '06 #5

P: n/a
I believe the Access (VBA) Round() function rounds every other value ending
in 5 up, then down, up, then down. It seeks to correct the long term
predudice of always rounding the five up.
"Cygnus" <sa*@firstbooks.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...
Sorry in advance for the lack of formatting in this posting.

Data:

(column headers)
Net Sales | Royalty Rate | Total Royalty
(data)
4.31 | 50.00% | 2.15
19.35 | 50.00% | 9.68

What gives?

Here is the query that runs this calculation:

UPDATE
[WT_Royalties-detail]
SET
[WT_Royalties-detail].[Total Royalty] = Round([Net Sales]*[Royalty
Rate],2)
WHERE
((([WT_Royalties-detail].Ref)="LSI"));

So why is the first row rounding down while the second row is rounding
up?

Note: Before I added the Round() function, the rows returned 2.155 and
9.675 respectively, if that helps.

Thanks!

Oct 12 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.