Rounding in a report field

  • Thread starter Thread starter Will D
  • Start date Start date
W

Will D

I have the following formula in an Access Report field:

=Round([PRINCIPAL]*[D_RATE]/360*([M_DATE]-[I_DATE]),2)

where PRINCIPAL = 425,000;
D_RATE = .0162;
M_DATE = 09/19/2003; and
I_DATE = 10/06/2003.

Therefore, [PRINCIPAL]*[D_RATE]/360*([M_DATE]-[I_DATE]) =
325.125.

How can I get Access 2002 to calculate this to 325.13
instead of 325.12? I have the Format set to Standard and
the Decimal Places set to Auto.

Thank you.
 
When I went to school, we were also taught to round .5 down. However, this
is statistically incorrect.

If you round .1 through .5 downwards, and .6 though .9 upwards, you are
unbananced (5 numbers down, and 4 up). The accepted solution is to round
towards the even number, e.g.: 2.5 gets rounded down (towards the even digit
2), but 3.5 gets rounded up (towards the even digit 4). That's how Access
works: 325.125 will be rounded down to 325.12, but 325.135 will be rounded
up to 325.14.

If you don't want it to work that way, you need your own rounding function.
Ken Getz has provided one that allows you to optionally turn on/off this
"banker's rounding":
http://www.mvps.org/access/modules/mdl0054.htm
Be sure to rename it so it doesn't fight with the built-in one.
 
-----Original Message-----
When I went to school, we were also taught to round .5 down. However, this
is statistically incorrect.

If you round .1 through .5 downwards, and .6 though .9 upwards, you are
unbananced (5 numbers down, and 4 up). The accepted solution is to round
towards the even number, e.g.: 2.5 gets rounded down (towards the even digit
2), but 3.5 gets rounded up (towards the even digit 4). That's how Access
works: 325.125 will be rounded down to 325.12, but 325.135 will be rounded
up to 325.14.

If you don't want it to work that way, you need your own rounding function.
Ken Getz has provided one that allows you to optionally turn on/off this
"banker's rounding":
http://www.mvps.org/access/modules/mdl0054.htm
Be sure to rename it so it doesn't fight with the built- in one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Will D said:
I have the following formula in an Access Report field:

=Round([PRINCIPAL]*[D_RATE]/360*([M_DATE]-[I_DATE]),2)

where PRINCIPAL = 425,000;
D_RATE = .0162;
M_DATE = 09/19/2003; and
I_DATE = 10/06/2003.

Therefore, [PRINCIPAL]*[D_RATE]/360*([M_DATE]-[I_DATE]) =
325.125.

How can I get Access 2002 to calculate this to 325.13
instead of 325.12? I have the Format set to Standard and
the Decimal Places set to Auto.

Thank you.
Click to expand...


.
Thank you for your reply.
Click to expand...
 
-----Original Message-----
=Round([PRINCIPAL]*[D_RATE]/360*([M_DATE]-[I_DATE]) + 0.001,2)
--
Ken Snell
<MS ACCESS MVP>

Will D said:
I have the following formula in an Access Report field:

=Round([PRINCIPAL]*[D_RATE]/360*([M_DATE]-[I_DATE]),2)

where PRINCIPAL = 425,000;
D_RATE = .0162;
M_DATE = 09/19/2003; and
I_DATE = 10/06/2003.

Therefore, [PRINCIPAL]*[D_RATE]/360*([M_DATE]-[I_DATE]) =
325.125.

How can I get Access 2002 to calculate this to 325.13
instead of 325.12? I have the Format set to Standard and
the Decimal Places set to Auto.

Thank you.
Click to expand...


.
Thank you for your reply.
Click to expand...
 
Back
Top