1

Please accept my sincerest apologies if the title or question is confusing.

I am using VBA in Excel 2007. I need to round a number to the nearest 500.

I am using the code:

Rvariable = Round(variable / 500, 0) * 500

My available numbers that I am trying to round go from -500 to 10000. If the value to be rounded is from 501 to 749, it will round down appropriately (i.e. 2501 to 2749 rounds to 500). 750's appropriately round up (2750 rounds to 3000). Values from 001 to 249 also round down correctly, and 251 to 499 round up correctly.

For some reason, a 250 value still rounds down (2250 rounds to 2000 not 2500). It will not round up until 251. When I use this function in the actual excel worksheet, the Round function works properly and will round up at 250.

Matteo NNZ
  • 11,930
  • 12
  • 52
  • 89
zigmus64
  • 13
  • 3
  • `Rvariable = Application.WorksheetFunction.Ceiling(variable, 500)`. Your way does not work because of [floating point precision](http://stackoverflow.com/q/21895756/11683). – GSerg Feb 11 '15 at 20:29
  • In addition to GSerg's comment, when you Round(2250/ 500, 0), it gives 4 instead of 4.5, thus 4 * 500 = 2000. Round(2251/500) gives 5, where 5*500 = 2500. – Alex Feb 11 '15 at 20:31
  • [Rvariable = Application.WorksheetFunction.Ceiling(variable, 500)] will not work in my situation since anything above each set of [500] goes to the next [500] (i.e. [8001] goes to [8500] when it should go to [8000]) – zigmus64 Feb 11 '15 at 21:33

1 Answers1

0

vba doesn't round in the traditional sense I learned(a bit of a hack) to add 0.000001. so you're not changing your code much by doing the below

Rvariable = Round((variable + 0.000001) / 500, 0) * 500

it isn't a significant amount and you shouldn't experience any calculation errors. although you'd probaly have to add in some logic for doing negatives -0.000001 instead for negative numbers

jamesC
  • 422
  • 6
  • 25
  • So now 500 will round to 1000. Also VBA [does round in the traditional sense](http://support.microsoft.com/kb/194983/en-us), it's just there is more than one traditional sense. – GSerg Feb 11 '15 at 20:51
  • no 500 will stil be 500 but Yes @GSerg great link I do need to broaden my traditions. but yeah this version leaves the issue in place for negatives so kind of getting away from my idea of not changing zigs code much. sadface – jamesC Feb 11 '15 at 21:09