Special round of numbers

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

Hi,

In a formula try:

Please Login or Register  to view this content.

In VBA try:

Please Login or Register  to view this content.

Lewis

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

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:

Please Login or Register  to view this content.

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

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.

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

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)

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

This also appears to work.

Formula:

Please Login or Register  to view this content.