Special round of numbers

[YasserKhalil]

Hello everyone
I need to round the following numbers as illustrated:
156.381 >> 156.38
156.382 >> 156.38
156.383 >> 156.38
156.384 >> 156.38
156.385 >> 156.38

156.386 >> 156.39
156.387 >> 156.39
156.388 >> 156.39
156.389 >> 156.39

Hope it is clear

[LJMetzger]

Hi,

In a formula try:

=ROUND(A1-0.0001,2)

In VBA try:

Sub SpecialRounding()

  Dim x As Double
  Dim y As Double
  
  For x = 156.38 To 156.42 Step 0.001
    y = Application.WorksheetFunction.Round(x - 0.0001, 2)
    Debug.Print Format(x, "0.000"), Format(y, "0.00")
    
  Next x

End Sub

Lewis

[YasserKhalil]

Thanks a lot for reply
I tested both the formula and the code .. It's ok except for this value 156.385 which should be 156.38 not 156.39
Thanks a lot for help

[LJMetzger]

Hi,

Thanks for the rep points. I thought I had the 156.385 correct, but obviously not. I will get back to you tomorrow, unless someone else gets there first.

Lewis

[MrShorty]

It looks like you are trying to implement a variation of banker's rounding. Microsoft's explanation of different rounding algorithms implemented in various products: https://support.microsoft.com/en-us/kb/196652

Note that the VBA round function uses banker's rounding. If I have correctly interpreted your goal, the easiest approach may be a simple VBA UDF. Something like:

function roundbank(dblnumber as double, intdigit as long) as double
'any error checking you would like
roundbank=round(dblnumber,intdigit)
end function

[YasserKhalil]

Thanks MrShorty for offering help
I really can't interpret it to UDF function
Generally thanks for helping me

[MrShorty]

Can you confirm that it is banker's rounding (round .5 to the nearest even digit) you are trying to implement? Even if you cannot use a UDF to accomplish the task, it will help us think through native Excel functions if we know for sure what we are trying to do.

[YasserKhalil]

In fact I illustrated the expected results in my first post ..
There are three digits after point ..
and as for the third digit after the point from 1 to 5 to be ignored and from 6 to 9 to round up the second digit by one
156.381 >> 156.38
156.382 >> 156.38
156.383 >> 156.38
156.384 >> 156.38
156.385 >> 156.38

156.386 >> 156.39
156.387 >> 156.39
156.388 >> 156.39
156.389 >> 156.39

[MrShorty]

So it is a "always round 0.5 down" rather than banker's rounding. (I came to the wrong conclusion because the 8 in your example is even and you did not include an odd example).

I would probably do something similar to what LJMetzger suggested, but, instead of adding 0.0001, subtract 0.0001.

=ROUND(number-0.0001,2)

[YasserKhalil]

Yes that's perfect
Thanks a lot for this simple and great formulas
Thank you very much

[newdoverman]

This also appears to work.

=MROUND(A1,0.01)