Hey folks,
I can't seem to understand this issue or how to fix it, hoping for some guidance.
I have a set of data, in column B, i want all the numbers to have 3 decimals places. Some naturally do in the data set but others don't so I formatted the column to be Numbers and add 3 decimal places.
This is fine at a glance but my issue is that is not the true value in the cell. In the specific cell it shows how I want it to show, but when i look at the formula bar, it is showing the value without the 3 decimal places and when i go to import, the system is also showing the value without the 3 decimal places.
I have tried copy and pasting the column, pasting values only or changing the format to text instead of numbers and nothing is working.
I am pulling my hair out trying to figure out where i am going wrong because it seems like such a simple task.
I think you may be misunderstanding what the numbers you're seeing in the formula bar are? Its the absolute value in the cell.
12345.000 = 12345. It doesnt matter that you can't see the three 0s in the formula bar, its the same number.
12345.100 = 12345.1 The missing 0s dont matter.
Excel is storing the absolute values and will show you the decimals in the formula bar when they exist in that cell. When you cross reference it with your database it will be correctif tha data there us also numeric.
The potential issue could be if you dont want values to have more than 3 decimal places because of rounding errors. In that case you would need to apply a formula to convert the numbers to be truly 3 decimal spaces.
For example 12345.001 is not exactly the same as 12345.0005 as its been rounded up. Excel would have the number with all decimal places (within limits) and that could mismatch the data in your database if its stored to 3 decimal place. Then you might want to convert your data so it properly rounds the numbers as new absolute values to work with.