Excel

197 readers

21 users here now

A community dedicated to any discussion about Excel. QnA, Discussion of other spreadsheet applications and ancillary programming/scripting are also welcome.

founded 2 years ago

MODERATORS

[email protected]

Value in cell is not true (lemmy.world)

submitted 1 day ago by [email protected] to c/[email protected]

11 comments fedilink hide all child comments

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.

top 11 comments

sorted by: hot top controversial new old

[–] [email protected] 10 points 1 day ago (1 children)

If I'm understanding right, you're worried about the number in your formula bar not matching the formatting of the highlighted cell. That's intended, the formula bar is showing the actual data. The cell is that data formatted how you request, not the other way around. Just like how if you formatted the cell to have red text it would not be red in your formula bar.

If you absolutely must have it have .000 at the end, convert your numbers to strings.

[–] [email protected] 2 points 1 day ago (3 children)

yeah it absolutely has to have the .000 because it will be using that to reference another database to sync based on that client id. How do I convert numbers to strings?

[–] [email protected] 3 points 1 day ago* (last edited 1 day ago)

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.

[–] [email protected] 2 points 1 day ago

Someone else posted a formula to convert the existing numbers to strings, but you can also just put a backtick(') in front of each number (and add the .000 as needed)

[–] ICastFist 2 points 1 day ago

Unless the values on the other database are strings instead of numbers, 12345.000 == 12345 will be true

[–] [email protected] 4 points 1 day ago

Try adding a column with the formula in row 2, =TEXT(B2, "0.000") , and fill down for other rows.

The results should give you the text representation you need.

[–] [email protected] 2 points 1 day ago (1 children)

The values are the same in the cell and the formula bar. There is a difference in formatting. You can control the formatting of the cell, but will not have the same degree of formatting with the formula bar. The role of the formula bar is to show you the true contents of the cell, and the sheet itself is where we are concerned with looks and formatting.

Not trying to be obtuse, but I’m not seeing the problem here. Is there a task that is failing because of the difference in formatting?

[–] [email protected] 1 points 1 day ago* (last edited 1 day ago) (1 children)

Not coming off as obtuse, and i'll try my best to explain the issue but I do understand what you are saying.

The goal is I am doing an import of clients into our system for an integration so the info syncs correctly and we sync using the Client ID, which is case sensitive.

When I imported the data into my system, i want to import the Client ID - 1105416.000 (what i see in the cell). When using my importer tool to match the corresponding fields in our system with the field in the spreadsheet, the value example the importer tool is showing is Client ID - 1105416, which is incorrect. I ran a small sample to test and it imported as 1105416, but it has to be 1105416.000

[–] [email protected] 4 points 1 day ago

Gotcha. You will need to import and format as text, not numbers. Depending on your importing tool and your destination file you may need to first dump the data into another sheet prior, clean up formatting, then paste values into the destination.

[–] [email protected] 0 points 1 day ago (1 children)

=Round(B1,3)

[–] [email protected] 1 points 1 day ago

ChatGPT gave me the same suggestion but didn't fix the issue.