sgreen wrote:With Cells formatted as Number I enter the number in the cell: {picture}
What the number gets changed to after hitting enter: {picture]
Numbers in Calc are limited to about 15 decimal digits of precision. When you enter 8627104496619111, unless the cell is formatted as text, Calc stores the value as 8.62710449661911E+015. You can format it with 16 digits but since Calc only could keep the most significant 15 digits, the final "1" is lost and displayed as 0.
sgreen wrote:If I format cells as Text the number doesn't get changed BUT it is not allowing me to drag the number in series like it used to. Now it changes the number to something entirely different, sometimes a random negative number: {picture}
When cells contain
text, the Autofill algorithm copies any non-numeric prefix, then increments a numeric suffix, performing integer 32-bit arithmetic, that is integers in the range -2147483648:2147483647. Converting 8627104496619111 to binary is
11110 10100110 01001110 01001010 11110110 00001110 01100111. The bits in grey are the ones which exceed 32 bits; they are not used. The remaining bits 01001010 11110110 00001110 01100111 have the decimal value 1257639527 and they are incremented to produce the values you see 1257639528, 1257639529, ….
sgreen wrote:Any ideas as to why the column isn't filling correctly?
It is. The preceding paragraphs explain why. Presumably you now want to ask:
How can I make the column fill look as if Calc was performing 16-digit arithmetic?
You have two choices. Entering the 16-digit number as text, you can fill successive cells with a formula (same idea as
Villeroy's).
Or, entering the number as 12-digits with a special format code to supply the first four ("8627") for display, you can fill successive cells with Autofill since Autofill with
numbers uses 15 digits of precision. The attached spreadsheet illustrates both methods.
If you have any further questions and would like assistance from me
attach a document demonstrating the situation
(remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).
Villeroy wrote:A quick test shows that this feature works well with numbers or with numeric strings up to 9 digits.
Yes, because 10⁹ is less than 2³¹. both text and numeric Autofill will work as one expects.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6. The locale for any menus or Calc formulas in my posts is English (USA).