Question about Text Format in Worksheet - why is it trimming decimal digits when reading from other cell

[ashot]

Does anyone know if Text in Worksheet Cell has a limitation for the length when it shows digits?

 

@Pat Stanford Please see attached VW file.

In my project I need to read Latitude and Longitude of my stakes in two different columns formatted to show either 9 or 12 Decimal Places Digits.

 

Then I am trying to use Concat function, but first I need to understand why the TEXT format in the last column trims and rounds my 12 digits number into 8 digit maximum?Test TEXT length in Worksheet.vwx

[Pat Stanford]

It appears that a VW Worksheet only supports 11 digits after the decimal point. @Vlado Can you comment on this?

 

If you format a cell as Decimal you can specify additional digits for display, but I was not able to find a way to enter those digits.

 

Apparently 8 digits in Lat/Long is adequate to get millimeter accuracy.  Do you really need more than that?

 

As for your other question about Why E2 + F2 was returning a number. Formatting a cell as text does not change what is actually stored in the cell which in this was was a number. So you can use the + sign to add the values.

 

=CONCAT(B9, ', ', C9)

Will take a number with up to 9 decimal places in the cells, or text in the cells and return them in a single cell separated by a comma.

 

If you format a cell as a Decimal Number with a certain number of decimal places and then convert the display of the cell to Text, the text will use that number of decimal places (up to 11).

 

HTH

[Vlado]

@ashot you can use the TXT function to convert to string: https://developer.vectorworks.net/index.php?title=Worksheet_Functions#Text

Example: =TXT(B2, 'General', 'Dec', '0.0000000000000')

 

I'm looking at the other part of the question.

 

 

[Pat Stanford]

Thanks Vlado. But I think TXT has a limitation also.

 

=TXT((B7+0.0000000000001), 'Decimal', '0.0000000000000000000000', 'No Units', 'Trailing Zero')

 Where B7 has a number with an accuracy of 11 decimal points. Adding the 1 in the 13th decimal place still displays only 11 decimal points even though I have the TXT set to specify 20.  It also seems to ignore the Trailing Zero parameter.

 

If you set TXT to use scientific notation 'Sci", then you get more decimals, but they are wrong.

 

B7 is the number in B2.  B8 is the number in B2 set to display as Text.  B9 is the TXT with Decimal output.  B10 is TXT with the output set to Sci.

 

 

 

[Hugues]

 

On 2/29/2024 at 12:04 AM, ashot said:

Then I am trying to use Concat function, but first I need to understand why the TEXT format in the last column trims and rounds my 12 digits number into 8 digit maximum?

 

When a cell with a decimal number is formatted to "General", the default number of decimal places displayed is 3. If the cell is later formatted to a format that allows specifying the number of decimal places, the number of decimal places specified will be remembered when the cell is formatted back to "General" or another format.

 

When you format a cell to "Text", it is not changing the value of the cell. Your number remains the same. It simply instruct the worksheet to treat that cell as a text as opposed to a number. You are seeing 8 decimal places instead of 12 because the internal decimal format for that cell is set to 8 decimal places from its previous formatting.

 

Remember that when you reference a cell, you are referencing it's actual value and not its display format. The format of the cell you are referencing into will determine how it will display.

 

As @Vlado mentioned, if you have a number that you want to convert to Text, the text cell format isn't what you need. You should use the TXT function.

 

 

[Vlado]

Thank you Hugues ☝️

[Hugues]

@Pat Stanford @ashot Here is the answer the question about the precision in worksheets.

 

The IEEE 754 specification standard for how to store and calculate floating-point numbers doesn't offer infinitely precise calculations. That standard can only accurately represent 15 digits decimal numbers. 

Worksheets follow the IEEE 754 specification but move the limit down to 13 digits to allow for handling of various rounding errors. I believe Excel follows the specification with 15 digits.

 

This is the reason why decimal numbers in worksheets have a precision of max 13 digits. 
Note that the number of digits in the specification here is the number of digits for the number, not the number of decimal places. 
@Pat Stanford You are seeing max 11 decimal places because your number already uses 2 digits for its whole number portion.

 

 

Hope it helps.

 

[Pat Stanford]

Thank you Hughes. You are correct the the number of digits vs the number of decimals was what I was not understanding.

 

@ashot  It looks like you are stuck with 11 decimal places if your integer part is 2 digits or 10 decimals if the integer part is 3 digits.

[ashot]

Thank you everyone.

 

So the Final Answer for this question:

for the Text field in Worksheet the

"decimal numbers in worksheets have a precision of max 13 digits. 
and that the number of digits in the Text field is the Total number of all digits referenced from the numerical field, including all decimal places  (whole part + fraction part).

[Hugues]

On 3/2/2024 at 2:41 PM, ashot said:

Thank you everyone.

 

So the Final Answer for this question:

for the Text field in Worksheet the

"decimal numbers in worksheets have a precision of max 13 digits. 
and that the number of digits in the Text field is the Total number of all digits referenced from the numerical field, including all decimal places  (whole part + fraction part).

 

I would like to clarify that this is not about cell formatted as Text or Text field. This is about decimal numbers in general.