Excel Rounding Issue

[love and hate excel]

I would like to round these three numbers to 4 decimal places using formula.

Initial Data​	Required Result​
37464.0120999999​	37464.0121​
37464.0121999999​	37464.0122​
37464.0122999999​	37464.0123​

I then want to copy and paste values on the formula. (It’s for a large volume of data so I want the end user to avoid any calculations)

I would like the numbers stored as numbers, not text. (I want to avoid using “Text to columns” function to fix it)

Is it possible???

 

[Fluff]

Hi & welcome to MrExcel.
How about

=ROUND(A2,4)

 

[love and hate excel]

Hi & welcome to MrExcel.
How about

=ROUND(A2,4)

I tried that, unfortunately it didn't work - when I copied and pasted the values, the middle number reverted to more than 4 decimal places:

37464.0121​

37464.0121999999​

37464.0123​

 

[Fluff]

Try

=TRUNC(ROUND(A2,4),4)

 

[love and hate excel]

Try

=TRUNC(ROUND(A2,4),4)

Thanks, gets me much closer but still not 100% accurate
(The second number now has 4 decimal places but unfortunately it is the same as the first number)

37464.0121​

37464.0121​

37464.0123​

 

[Fluff]

Not sure there's much you can do about it. It's down to floating point precision.

 

[love and hate excel]

Not sure there's much you can do about it. It's down to floating point precision.

Thank you, at least I know it's not possible - your final suggestion of using TRUNC get's me 99.9999% of the answer

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Dave Patton]

It would help if you posted your data with the forum's tool named XL2BB.
Please ensure that you do not have extraneous characters in your data.

T202303a.xlsm
	A	B
1		Display 4 decimal places
2	37464.0120999999	37,464.0121
3	37464.0121999999	37,464.0122
4	37464.0122999999	37,464.0123
4c

Cell Formulas
Range	Formula
B2:B4	B2	=ROUND(A2,4)

 

[Fluff]

Maybe you missed posts#2 & 3