Round Inconsistency

**davegugg** · 09-14-2010, 04:01 PM

On my Excel 07, the worksheet round function rounds 4.5 (to zero decimal places) to 5. In vba, the round function rounds 4.5 (to zero decimal places) to 4. What?

**shg** · 09-14-2010, 04:10 PM

VBA Round uses banker's rounding; the ROUND function does not.

The worksheet ROUND function is available to VBA.

**davegugg** · 09-14-2010, 04:31 PM

Well, I'm glad that was common knowledge to you. I have to go back through a project I'm working on and reload a bunch of data into my workbook because I assumed they would be the same. I've lost out on a half day of work and I looked like an ametuer to my coworkers.

PS, I work in a bank but I still had to look up what banker's rounding was. I'm surprised anyone would ever want that. How would that be programmatically advantageous?

**shg** · 09-14-2010, 04:42 PM

See http://en.wikipedia.org/wiki/Roundin...d_half_to_even

Note particularly,

It is the default rounding mode used in IEEE 754 computing functions and operators (and in various computing languages such as ANSI/ISO C, C++, and Java, for their float and double types).

At a glance, I can't find confirmation of any those statements, but the intent is to reduce bias when you're summing a lot of rounded numbers.

**davegugg** · 09-14-2010, 05:03 PM

Interesting, I can see why it would be valuable for reducing bias. I guess that was something I would have learned if I had actual programming education. Thanks shg.

**Andrew-R** · 09-15-2010, 02:38 AM

In VBA I rarely use the round function, I'd normally use

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Depending on how I wanted to round. I suppose it's a hangover from coding in the days before there was a round function, but it does mean that I don't get caught out with unexpected values being returned.

**Cheeky Charlie** · 09-16-2010, 05:21 AM

Worth mentioning that things like 10*int(x/10) can be used to round to any kind of decimal precision, like round, and more to the point, things like 5*int(x/5) can be used to round to any interval.

(not that you guys didn't know that, just in case someone chances on the thread!)

**Andrew-R** · 09-16-2010, 06:25 AM

I'd normally go with

Please Login or Register  to view this content.

Where r is the number of decimal places required.

**ChemistB** · 09-16-2010, 12:16 PM

Please Login or Register  to view this content.

This is different from Excel's normal rounding procedure. Excel normally uses symetrical rounding around 0, whereas this produces asymetrical rounding.

For example, -1.2345 rounded to 3 decimal places;
Using ROUND(-1.2345,3) gives -1.235 which is rounding Down

The formula above gives -1.234 which is rounding Up (which is what you would expect when the next and final digit is 5).

As most of my worksheets are made for the workplace, it's important to know what their rounding policies are.

**Andrew-R** · 09-17-2010, 06:54 AM

I started playing with this last night and it seems to expose one of Excel's little problems.

If you start with the value 1.2345 and put it into my rounding function with r=3 then Excel reports

x * 10^3 = 1234.5
x * 10^3 + 0.5 = 1235
Int(x*10^3+0.5) = 1234
Int(x*10^3+0.5)/10^3 = 1.234

So there's a calculation error between steps 2 and 3 that seems linked to how x and r are defined in the function. If I set them both as variants then it works fine, if I define x as a double and r as an integer it gives the error above.

Any thoughts?