Algorithm in OpenOffice Fill

Discuss the spreadsheet application

Algorithm in OpenOffice Fill

Postby atzi » Sun Oct 21, 2012 10:38 pm

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 3341 times
Last edited by Hagar Delest on Sun Oct 21, 2012 11:09 pm, edited 1 time in total.
Reason: pic embedded.
OpenOffice 3.1 on Windows 7
atzi
 
Posts: 1
Joined: Sun Oct 21, 2012 10:13 pm

Re: Algorithm in OpenOffice Fill

Postby MrProgrammer » Mon Oct 22, 2012 4:04 am

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

Re: Algorithm in OpenOffice Fill

Postby acknak » Mon Oct 22, 2012 6:23 am

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
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Algorithm in OpenOffice Fill

Postby kingfisher » Mon Oct 22, 2012 6:35 am

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

Re: Algorithm in OpenOffice Fill

Postby Charlie Young » Mon Oct 22, 2012 9:02 am

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 18 guests