3

I had an application for returning closest matches to certain values in a large cluster of values( as in my earlier question) and I chose a VBA solution. While in the course of using the said application, I observed that the results for the value of 0.5 were not correct. I had been using the VBA Round function which I found to be returning 0 for 0.5 rounded to integer whereas the worksheet round function returns 1. Strangely, the VBA round function returns 2 for 1.5. I had to substitute the worksheet function in place of the VBA one.

Am I missing something?

Community
  • 1
  • 1
Vaibhav Garg
  • 717
  • 5
  • 15
  • 31
  • Keep in mind that the VBA function isn't "incorrect"; as Lance points out in the linked Access question, it's simply a different method of rounding. An example of incorrect rounding would be ColdFusion 6.1, I think, that rounded .5 up except in very specific cases. – Dave DuPlantis Nov 05 '08 at 20:33

1 Answers1

3

It's a known issue. The VBA Round() function uses Banker's rounding while the spreadsheet cell function uses arithmetic rounding. Check the details here:

PRB: Round Function different in VBA 6 and Excel Spreadsheet

The workaround proposed by Microsoft is to write a custom function to get the desired results.

Banker's rounding always rounds 0.5 to the nearest even number and is standard in accounting, which is why Excel works that way. Arithmetic rounding rounds 0.5 up to the next number.

Joel Spolsky
  • 33,372
  • 17
  • 89
  • 105
Panos
  • 18,992
  • 6
  • 45
  • 54
  • Your last paragraph is backwards: Excel *doesn't* use the Banker's rounding so saying that this is "why Excel works that way" makes little sense. If anything, it only deepens the mystery of why Excel broke from Banker's rounding. – John Coleman Apr 20 '16 at 21:13
  • @JohnColeman, the last paragraph was added by Joel Spolsky (who was the program manager of Excel team back in '90s), so I try to interpret it: "Because banker's rounding is applicable only in accounting, Excel preferred arithmetic rounding which has broader applicability". Hope not to make it worst... – Panos Apr 22 '16 at 08:45
  • 1
    Interesting. Since he is also the cofounder of Stack Overflow and the writer of one of the best programming blogs of all time, I can see your hesitance in editing it, though I suspect that he simply misspoke. – John Coleman Apr 22 '16 at 10:19