[Solved] Dragging Fill Handle not correctly increasing value

Discuss the spreadsheet application
Post Reply
sgreen
Posts: 4
Joined: Fri Sep 13, 2019 3:08 am

[Solved] Dragging Fill Handle not correctly increasing value

Post by sgreen »

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?
Last edited by RusselB on Sat Sep 14, 2019 4:18 am, edited 1 time in total.
Reason: Green check mark and Solved tag added. Subject line slightly modified to make room for Solved tag
OpenOffice 4.1.6 on MacOS 10.13.6
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Dragging Fill Handle is not correctly increasing value

Post by 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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
sgreen
Posts: 4
Joined: Fri Sep 13, 2019 3:08 am

Re: Dragging Fill Handle is not correctly increasing value

Post by 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.
OpenOffice 4.1.6 on MacOS 10.13.6
User avatar
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Dragging Fill Handle is not correctly increasing value

Post by 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?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
sgreen
Posts: 4
Joined: Fri Sep 13, 2019 3:08 am

Re: Dragging Fill Handle is not correctly increasing value

Post by sgreen »

With Cells formatted as Number I enter the number in the cell:
Number I want to enter into the Cell
Number I want to enter into the Cell
What the number gets changed to after hitting enter:
Number gets rounded
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
Number is not dragging in series
OpenOffice 4.1.6 on MacOS 10.13.6
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dragging Fill Handle is not correctly increasing value

Post by 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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4883
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Dragging Fill Handle is not correctly increasing value

Post by MrProgrammer »

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.
201909131109.ods
(15.38 KiB) Downloaded 85 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).
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, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
sgreen
Posts: 4
Joined: Fri Sep 13, 2019 3:08 am

Re: Dragging Fill Handle is not correctly increasing value

Post by 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!
OpenOffice 4.1.6 on MacOS 10.13.6
Post Reply