Denver Area Access Users Group

Microsoft Access, SQL Server, and Excel all provide one function for rounding numbers called ROUND. This implements a rounding algorithm commonly know as the Banker’s Round (AKA Round Half Even). Excel also provides two other versions called ROUNDUP and ROUNDDOWN.

But there are many different ways to round numbers. If you are getting your data from another source and you need your calculations to match that source’s calculations, you may need to use a different algorithm. Indeed, you may need to contact the source to find out what algorithm they have used. Also, errors can creep in when you need to implement multiple rounds e.g. a further round of an already rounded value.

A sample database will be provided on the DAAUG website that contains a VBA code module which implements a dozen different algorithms. You can use this directly in your own projects by importing the module!

A ‘gotcha’ and work around will be discussed concerning using DoEvents inside (math) functions that are going to be called thousands of times at a pop.

Download the presentation materials