## [Solved] Algorithm in OpenOffice Fill

### [Solved] Algorithm in OpenOffice Fill

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

OpenOfficeFill.jpg (10.24 KiB) Viewed 3897 times
Last edited by MrProgrammer on Wed May 27, 2020 1:50 am, edited 2 times in total.
Reason: pic embedded.; Tagged ✓ [Solved]
OpenOffice 3.1 on Windows 7
atzi

Posts: 1
Joined: Sun Oct 21, 2012 10:13 pm

### 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 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, ….
Note that if the 12 cells all contain the same number, this is an arithmetic progression (with a common difference of zero) and any new cells are filled with the same number because this will continue the arithmetic progression.

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 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3898
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, 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
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

### 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.6 on PCLinuxOS

kingfisher
Volunteer

Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

### Re: Algorithm in OpenOffice Fill

Using formulas:

If the first number, 50, is in M2, then in M3:

Code: Select all   Expand viewCollapse view
`=M\$2+INT((ROW()-ROW(M\$2))/12)`

Copy M3 down as far as needed.

If desired,copy and Paste Special to replace formulas with values.
Apache OpenOffice 4.1.1
Windows XP

Charlie Young
Volunteer

Posts: 1559
Joined: Fri May 14, 2010 1:07 am