Rounding

  • Thread starter Thread starter Shoe
  • Start date Start date
S

Shoe

If I remember correctly when rounding to the nearest, the number 5 is the
cutoff for rounding up.
When I use the ROUND function in VBA for ACCESS or EXCEL that does not happen.
If I run the function: ROUND(4.25, 1) I get 4.2 as the result.
If I run the function: ROUND(4.26, 1) I get 4.3 as the result.

Now If I go to Excel and run the ROUND function in the Cells I get 4.3 as a
result for both ways above.

Am I doing something wrong or does VBA use 6 as the cutoff for rounding to
the nearest?

Thanks.
 
Shoe said:
If I remember correctly when rounding to the nearest, the number 5 is
the cutoff for rounding up.
When I use the ROUND function in VBA for ACCESS or EXCEL that does
not happen. If I run the function: ROUND(4.25, 1) I get 4.2 as the
result.
If I run the function: ROUND(4.26, 1) I get 4.3 as the result.

Now If I go to Excel and run the ROUND function in the Cells I get
4.3 as a result for both ways above.

Am I doing something wrong or does VBA use 6 as the cutoff for
rounding to the nearest?

Thanks.
Click to expand...

Access uses Banker's rounding meaning 5 does not always round up. Rather it
rounds to the even value. This should make approximately half the 5s round
up and half of them round down so drift is not introduced when large columns
of numbers are rounded.

If you don't want that behavior you can create your own rounding function.
 
If I remember correctly when rounding to the nearest, the number 5 is the
cutoff for rounding up.
When I use the ROUND function in VBA for ACCESS or EXCEL that does not happen.
If I run the function: ROUND(4.25, 1) I get 4.2 as the result.
If I run the function: ROUND(4.26, 1) I get 4.3 as the result.

Now If I go to Excel and run the ROUND function in the Cells I get 4.3 as a
result for both ways above.

Am I doing something wrong or does VBA use 6 as the cutoff for rounding to
the nearest?

Thanks.
Click to expand...

It's not a cutoff issue; it's "Banker's Rounding". A number ending less than 5
rounds down; more than 5 rounds up; and a number ending in exactly 5 rounds to
the nearest *even* value in the next digit. That is, 4.25 rounds to 4.2, but
so does 4.15.

The rationale is that always rounding up will - on average! - increase the
value. The average of an ensemble of rounded numbers will be (in general)
larger than the average of the unrounded numbers. Rounding sometimes up and
sometimes down keeps the average closer.
 
Thanks.

Rick Brandt said:
Access uses Banker's rounding meaning 5 does not always round up. Rather it
rounds to the even value. This should make approximately half the 5s round
up and half of them round down so drift is not introduced when large columns
of numbers are rounded.

If you don't want that behavior you can create your own rounding function.
Click to expand...
 
Back
Top