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?
[Solved] Dragging Fill Handle not correctly increasing value
[Solved] Dragging Fill Handle not correctly increasing value
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
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
Re: Dragging Fill Handle is not correctly increasing value
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.
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.
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.
Re: Dragging Fill Handle is not correctly increasing value
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
Re: Dragging Fill Handle is not correctly increasing value
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.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Dragging Fill Handle is not correctly increasing value
With Cells formatted as Number I enter the number in the cell:
What the number gets changed to after hitting enter:
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:
OpenOffice 4.1.6 on MacOS 10.13.6
Re: Dragging Fill Handle is not correctly increasing value
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)
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Dragging Fill Handle is not correctly increasing value
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:With Cells formatted as Number I enter the number in the cell: {picture}
What the number gets changed to after hitting enter: {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: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}
It is. The preceding paragraphs explain why. Presumably you now want to ask:sgreen wrote:Any ideas as to why the column isn't filling correctly?
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).How can I make the column fill look as if Calc was performing 16-digit arithmetic?
Yes, because 10⁹ is less than 2³¹. both text and numeric Autofill will work as one expects.Villeroy wrote:A quick test shows that this feature works well with numbers or with numeric strings up to 9 digits.
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).
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).
Re: Dragging Fill Handle is not correctly increasing value
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