[Solved] Algorithm in OpenOffice Fill

Discuss the spreadsheet application
Post Reply
atzi
Posts: 1
Joined: Sun Oct 21, 2012 10:13 pm

[Solved] Algorithm in OpenOffice Fill

Post by atzi »

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
OpenOfficeFill.jpg (10.24 KiB) Viewed 5601 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
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Algorithm in OpenOffice Fill

Post by MrProgrammer »

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, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Algorithm in OpenOffice Fill

Post by acknak »

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
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Algorithm in OpenOffice Fill

Post by kingfisher »

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
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Algorithm in OpenOffice Fill

Post by Charlie Young »

Using formulas:

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

Code: Select all

=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
Post Reply