Problem with Rounding

Perhaps try the ROUNDDOWN function

Yes I checked, and your question/remark is??

The only unusual entry I see in your pictured list is the 5th entry x.125 where Excel's ROUND() function rounds up to x.13 and AutoCad appears to be using Banker's rounding where it round to the nearest even (so x.125 rounds down to x.12).

The other possibility is floating point errors. In Autocad, perhaps Autocad is seeing the value to its full precision and it sees x.12499999999, which naturally rounds down to .12. When exported to Excel with only 3 digits, this part of the information is lost and Excel sees x.125. Then, using Excel's "standard" round 5 up approach, it rounds this up to x.13.

Those are two possible explanations for the behaviour you are seeing. As Pepe Le Mokko says, what is your question?

Originally Posted by AhsanQatar

In AutoCAD Rounding
( 0.005 ≥ x ) = 0.00
( 0.005 < x ) = 0.01

In Excel Rounding
( 0.005 > x ) = 0.00
( 0.005 ≤ x ) = 0.01

Your question is clear to me, to wit: how can we coerce Excel to round the same way that AutoCAD does?

The problem is: you have not provided enough examples (or a detailed explanation) for us to know how AutoCAD rounds "half" (half of 0.01, in this case).

It might round half to even (a.k.a. banker's rounding). So, 123.135 rounds down to 123.14, just as 123.125 rounds to 123.12.

It might round half down, as you describe. So, 123.135 rounds to 123.13, just as 123.125 rounds to 123.12.

Also, please format the "database" column as Number with 13 decimal places, to display 15 significant digits.

We need to know if the data from AutoCAD actually has more than 3 decimal places.

If it does, it would be helpful to see examples like 123456.1254 and 123456.1256, as well as 123456.1354 and 123456.1356.

Presumably, AutoCAD rounds all of them up to 123456.13 and 123456.14 respectively. But stranger things have happened.

Finally, please follow the instructions in the bright yellow banner at the top of the webpage (hard to miss!), and attach an Excel file that demonstrates all of these examples instead of an image (PNG file).

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.