[Solved] Dragging Fill Handle not correctly increasing value

Discuss the spreadsheet application

[Solved] Dragging Fill Handle not correctly increasing value

Postby sgreen » Fri Sep 13, 2019 3:12 am

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
sgreen
 
Posts: 4
Joined: Fri Sep 13, 2019 3:08 am

Re: Dragging Fill Handle is not correctly increasing value

Postby RusselB » Fri Sep 13, 2019 4:27 am

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 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5661
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Dragging Fill Handle is not correctly increasing value

Postby sgreen » Fri Sep 13, 2019 4:31 am

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
sgreen
 
Posts: 4
Joined: Fri Sep 13, 2019 3:08 am

Re: Dragging Fill Handle is not correctly increasing value

Postby robleyd » Fri Sep 13, 2019 4:42 am

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
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3057
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Dragging Fill Handle is not correctly increasing value

Postby sgreen » Fri Sep 13, 2019 5:24 am

With Cells formatted as Number I enter the number in the cell:
Screen Shot 2019-09-12 at 8.20.15 PM.png
Number I want to enter into the Cell

What the number gets changed to after hitting enter:
Screen Shot 2019-09-12 at 8.20.21 PM.png
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:
Screen Shot 2019-09-12 at 8.23.45 PM.png
Number is not dragging in series
OpenOffice 4.1.6 on MacOS 10.13.6
sgreen
 
Posts: 4
Joined: Fri Sep 13, 2019 3:08 am

Re: Dragging Fill Handle is not correctly increasing value

Postby Villeroy » Fri Sep 13, 2019 6:46 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dragging Fill Handle is not correctly increasing value

Postby MrProgrammer » Fri Sep 13, 2019 7:04 pm

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 9 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 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3891
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Dragging Fill Handle is not correctly increasing value

Postby sgreen » Sat Sep 14, 2019 12:40 am

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
sgreen
 
Posts: 4
Joined: Fri Sep 13, 2019 3:08 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests