I had a specific need for a column to show the same number for 12 cells going down then increase that number by 1 for 12 more cells, then increase that number by 1 for 12 cell.....etc. Copy and paste or fill down, or fill down series or even autofill did not give an option.
What I did notice if I filled the first 24 cells then grabbed the fill handle in the bottom right corner of the last cell with a number, and dragged down, OpenOffice autofill would try to figure what series I wanted but could not replicate it. It would fill twice as many cells then add one number. Every 24 cells, instead of every 12 cells, the number went up by 1.
So I tricked it by filling in 6 of the first number and 6 of the next higher number then fill down and got the results I needed.
Wonder Why? Are there better ways?
Hope this might help someone.
Regards,
atzi
[Solved] Algorithm in OpenOffice Fill
[Solved] Algorithm in OpenOffice Fill
Last edited by MrProgrammer on Wed May 27, 2020 1:50 am, edited 2 times in total.
Reason: pic embedded.; Tagged ✓ [Solved]
Reason: pic embedded.; Tagged ✓ [Solved]
OpenOffice 3.1 on Windows 7
- MrProgrammer
- Moderator
- Posts: 4894
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Algorithm in OpenOffice Fill
Hi, and welcome to the forum.
I am not a developer, but I suspect that the autofill algorithm for twelve numbers (using the Fill handle) is:
If you put 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50.5 in the first 12 cells, this is not an arithmetic progression. Highlighting the 12 cells and using the fill handle, the next cells will contain 51, 51, 51, 51, 51, 51, 51, 51, 51, 51, 51, 51.5, 52, 52, 52, 52, 52, 52, 52, 52, 52, 52, 52, 52.5, …. Then you can select these cells and use Edit > Find & Replace > More Options > Current selection only. Search for .5, Replace with .0, and click Replace All to clear all the fractions.
If this answered your question 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.
I am not a developer, but I suspect that the autofill algorithm for twelve numbers (using the Fill handle) is:
- If the Command key is depressed on a Mac or the Ctrl key is depressed on other platforms then cell 13 contains the value in cell 1, cell 14 contains the value in cell 2, cell 15 contains the value in cell 3, ….
- If the 12 cells contain an arithmetic progression, that progression is continued into cells 13, 14, 15, ….
- Otherwise cell 13 is cell 1 incremented by 1, cell 14 is cell 2 incremented by 1, cell 15 is cell 3 incremented by 1, ….
If you put 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50.5 in the first 12 cells, this is not an arithmetic progression. Highlighting the 12 cells and using the fill handle, the next cells will contain 51, 51, 51, 51, 51, 51, 51, 51, 51, 51, 51, 51.5, 52, 52, 52, 52, 52, 52, 52, 52, 52, 52, 52, 52.5, …. Then you can select these cells and use Edit > Find & Replace > More Options > Current selection only. Search for .5, Replace with .0, and click Replace All to clear all the fractions.
If this answered your question 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.
Last edited by MrProgrammer on Sun Mar 02, 2014 5:24 am, edited 2 times in total.
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: Algorithm in OpenOffice Fill
Create first the simple (drag/fill) series that contains all the numbers you want in the end, say: 50, 51, 52, 53. 54, 55
Copy that to the clipboard, then paste eleven more copies in the same column
Select all the numbers and sort ascending
Result: 12 copies of each value in ascending series
Copy that to the clipboard, then paste eleven more copies in the same column
Select all the numbers and sort ascending
Result: 12 copies of each value in ascending series
AOO4/LO5 • Linux • Fedora 23
- kingfisher
- Volunteer
- Posts: 2123
- Joined: Tue Nov 20, 2007 10:53 am
Re: Algorithm in OpenOffice Fill
To get anything other than a simple increment, you need to establish a series. You can get the number in the first filled cell in a column to repeat by holding CTRL while dragging down. The method you used is probably as good as any.
Apache OpenOffice 4.1.9 on Linux
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Algorithm in OpenOffice Fill
Using formulas:
If the first number, 50, is in M2, then in M3:
Copy M3 down as far as needed.
If desired,copy and Paste Special to replace formulas with values.
If the first number, 50, is in M2, then in M3:
Code: Select all
=M$2+INT((ROW()-ROW(M$2))/12)
If desired,copy and Paste Special to replace formulas with values.
Apache OpenOffice 4.1.1
Windows XP
Windows XP