Rounding Help Please

  • Thread starter C.L.A \(PTY\) Ltd
  • Start date
C

C.L.A \(PTY\) Ltd

I'm currently sitting with a predicament, because I received a rounding
procedure which excel doesn't cater for. The reports require a rounding
procedure that isn't roundup, roundown, or round.

An Example:

If the digit following the last significant figure is a 5, the number is
rounded to the nearest even digit.

eg. 10.65 is rounded to 10.6
10.75 is rounded to 10.8
10.55 is rounded to 10.6
10.45 is rounded to 10.4

This only applies when the last digit is a 5, if any digit follows the 5,
the number is rounded up to the next digit.

eg. 10.651 is rounded to 10.7
10.451 is rounded to 1.5

It all sounds simple, but it is definately not.

Any help will be appreciated

Some References:
1. Analytical Chemistry - Gary D Christian, Chapter 2 Data handling
2. Chemistry - Michael Sienko and Robert Plane, Chapter 1.3 Management
3. Fundamentals of analytical chemistry -Skoog, West and Holler, Chapter
2 Errors in chemical analysis

Kindest regards,

Johan Els
 
H

Harald Staff

Hi Johan

This is usually called "Bankers rounding" because it's said (unconfirmed)
that banks round like that.

Open the VB editor (Alt F11 or similar). Menu Insert > Module. Paste this
in:

Function BANKROUND(Number As Double, Digits As Integer) As Double
BANKROUND = Round(Number, Digits)
End Function

Now return to Excel. In Cell B1:
=BANKROUND(A1,1)

Do also a Google newsgroup search on "bankers rounding", it's frequently
discussed with tons of other solutions. The one above is from Norman Harker.

HTH. Best wishes Harald
 
J

Jerry W. Lewis

The VBA Round() function does not work well with many decimal fractions,
and unlike the worksheet ROUND() function, does not accept negative
values of Digits. An improved VBA function is given under the separate
thread in microsoft.public.excel.charting

Johan, please do not separately post the same question to multiple
newsgroups.

Jerry
 
Top