[Solved] Dragging Fill Handle not correctly increasing value

[RusselB]

Hello and thanks for any help in advance.

I used to be able to grab the fill handle (bottom right of a row) and drag it down the column to increase the number by 1 as it filled the columns below. This is no longer working for me and instead is giving me negative numbers that are completely different from the first number when dragging down.

I've ensured all cells are formatted as Text, when I paste the number into the cell I do a Paste Special and ensure it's unformatted text. I can hold CMD and it will copy the exact same number instead of increasing (so this function still works). I also get the same negative number issue when I do an Edit > Fill > Series. Any ideas as to why the column isn't filling correctly?

[RusselB]

I'd suggest leaving the cells formatted as numbers.
The auto-fill works differently with text, so that might be why you are seeing what you are.
I can't think of a single reason to have numbers formatted as text, so I never do.
Having numbers formatted as text makes them much harder to work with for most, if not all, mathematical procedures.

[sgreen]

If I leave it on the Numbers format the number I'm pasting (even when pasted as unformatted text via Paste Special) always gets changed after hitting enter. Text format is the only way to prevent OpenOffice from changing the number once I've pasted it.

[robleyd]

Are you perhaps wanting to have leading zeros on your numbers? If not,can you describe exactly what you are doing and what is happening?

[sgreen]

With Cells formatted as Number I enter the number in the cell:

Number I want to enter into the Cell

What the number gets changed to after hitting enter:

Number gets rounded

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:

Number is not dragging in series

[Villeroy]

A quick test shows that this feature works well with numbers or with numeric strings up to 9 digits. Numbers are limited to 15 digits. Your numeric string has 16.
Use the rightmost digits for the fill operation and concatenate the result with the others.
B1: =LEFT(A1;LEN(A1)-6)&(RIGHT(A1;6)+1)

[MrProgrammer]

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.

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, ….

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.

201909131109.ods

(15.38 KiB) Downloaded 10 times

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).

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

[sgreen]

You all are awesome, and thank you Mr. Programmer for the detailed response. What you outlined (combined with concatenate) was the fix I needed. Thank you again!