Speeding up 'Fill Down' function

Discuss the spreadsheet application

Speeding up 'Fill Down' function

Postby colwyn » Mon Jul 28, 2008 10:47 am

I need to fill a formula from G2 down to G60000 but even doing this by copying and pasting, say, 3000 rows at a time, takes forever. As I have many sheets on which to do this - is there a quicker way ??
Thanks.
Colwyn.
OOo 3.0.X on Ms Windows XP
colwyn
 
Posts: 66
Joined: Tue Jul 01, 2008 9:53 pm
Location: Wales

Re: Speeding up 'Fill Down' function

Postby Villeroy » Mon Jul 28, 2008 11:06 am

What is the formula? Turn off automatic calculation, copy down and calculate. Which step is the time consuming one?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27306
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Speeding up 'Fill Down' function

Postby probe1 » Mon Jul 28, 2008 11:17 am

I do enter the formula in G2, then enter the range in the named range box (showing G2 by then, changed to G2:G60000), then Edit>Fill>Down, and voila!
Heavy complex formulas may need some (3-6) seconds.

What's your formula?
Cheers
Winfried

DateTime2 extension: insert date, time or timestamp, formatted to your needs
User avatar
probe1
Volunteer
 
Posts: 277
Joined: Mon Oct 08, 2007 1:34 am
Location: Chonburi Thailand

Re: Speeding up 'Fill Down' function

Postby colwyn » Mon Jul 28, 2008 1:12 pm

Villeroy, when you say "Turn off automatic calculation" - is this via Tools>Options>Oo calc>Calculate and then turn off iterations ???

The formula in G2 is:

{=IF(MAX(ROW(I$1:I1)*(I$1:I1=I2))=0;"";MAX(ROW(I$1:I1)*(I$1:I1=I2)))}

The time-consuming part is when I click 'Fill Down'

------------------------------------------------------------------------------------------

probe1, thanks - that makes a lot of difference. Still taking a lot more than 6 seconds though. Quite a few minutes in fact - but at least I don't have to scroll down 60,000 rows anymore!

Thanks guys.
Colwyn.
OOo 3.0.X on Ms Windows XP
colwyn
 
Posts: 66
Joined: Tue Jul 01, 2008 9:53 pm
Location: Wales

Re: Speeding up 'Fill Down' function

Postby Villeroy » Mon Jul 28, 2008 1:45 pm

menu:Tools>CellContents>AutoCalculation
menu:Tools>CellContents>Recalculate [key F9]

Databases are made for what you obviously try to do (getting the latest record of anything). They work performant with large amounts of data (millions of records).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27306
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Speeding up 'Fill Down' function

Postby colwyn » Mon Jul 28, 2008 3:14 pm

Villeroy, thanks - I've turned off auto calculate and it's certainly quicker although all hell may be let loose when I turn it back on after filling down the next six sheets! Have filled down 4 columns to row 54728 and am waiting for it to finish - it's been going for about 25 minutes so far. As I'm using OpenOffice 3 beta, I wondered if it would make any difference if I used OpenOffice 2.4?? Or could I perhaps save the file under something other than .odb??

I can see the logic in what you say about databases - but I just can't seem to understand them too well. I would need to input at least 6 sheets of work - each one having columns from A to A and rows 1 to approx 60,000. I've tried pasting data into base but it doesn't work and when I create a table it doesn't let me set formulae. I need to be able to input formulae and later change them should I need to do so.
OOo 3.0.X on Ms Windows XP
colwyn
 
Posts: 66
Joined: Tue Jul 01, 2008 9:53 pm
Location: Wales

Re: Speeding up 'Fill Down' function

Postby Villeroy » Mon Jul 28, 2008 3:41 pm

I can fill entire unformatted columns with simple formulas (=ROW(), =RAND()) within a second.
If you are not able to paste your data into a database this may indicate that your data are inconsistent already. For instance, you may have string values and numeric values mised in the same column, or you try to fill a column of small integers with at least one large integer, or you try to import a field as primary key although it has duplicates. In a spreadsheet there is no mechanism to prevent inconsitancies like this. In a spreadsheet you need some non-trivial formulas which constantly alert for duplicates and "wrong" values. These formulas decrease performance even further. Cell validation is not the way to go. You can clear (Del, Backspace), paste, drag&drop, insert, remove cells at any time. Spreadsheets are mainly for calculating numbers by position (relative references) while beeing very poor list keepers.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27306
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Speeding up 'Fill Down' function

Postby jrkrideau » Mon Jul 28, 2008 4:07 pm

colwyn wrote:Villeroy, thanks - I've turned off auto calculate and it's certainly quicker although all hell may be let loose when I turn it back on after filling down the next six sheets! Have filled down 4 columns to row 54728 and am waiting for it to finish - it's been going for about 25 minutes so far. As I'm using OpenOffice 3 beta, I wondered if it would make any difference if I used OpenOffice 2.4?? Or could I perhaps save the file under something other than .odb??

I can see the logic in what you say about databases - but I just can't seem to understand them too well. I would need to input at least 6 sheets of work - each one having columns from A to A and rows 1 to approx 60,000. I've tried pasting data into base but it doesn't work and when I create a table it doesn't let me set formulae. I need to be able to input formulae and later change them should I need to do so.

If I've understood this correctly you have something like 1 million cells? Is this mainly data or a mix of data and formulae?

If it's mainly data upon which you then need to perform some operations I'd strongly support Villroy's suggestion of a data base however I'd also suggest that a good statistics program ( R (Opensourece),SPSS,(not to expensive) , SAS ( exhorbitant) may be alternatives. I don't think a spreadsheet is really designed to do this type of work. It can but it's just the wrong tool.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3706
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Speeding up 'Fill Down' function

Postby colwyn » Mon Jul 28, 2008 5:58 pm

Rideaujrk, yes it involves both text and numbers. It is historical data covering past years and spans columns A-AG,rows 1-60,000 (approx) and each sheet covers one year. It can only function through formulae. It is only personal not business and I've no real knowledge of database. My spreadsheet seems to be causing my computer to sieze up and I'm just trying to find ways around this - if possible.
Colwyn.
OOo 3.0.X on Ms Windows XP
colwyn
 
Posts: 66
Joined: Tue Jul 01, 2008 9:53 pm
Location: Wales

Re: Speeding up 'Fill Down' function

Postby Villeroy » Mon Jul 28, 2008 8:14 pm

You have one sheet per year. How can you query records across years? In a database every sheet would be a table, and there are methods to merge tables permanently and temporarily.
OK, let's say you can afford a legal copy of the latest Excel (which allows for many more rows and should be faster) and merge the year-sheets into one. There may be still inconsistencies in the merged table which prevent reliable queries. All spreadsheets are extremely tolerant by design. Every single cell is allowed to take any possible value.
The formula you mentioned indicates a certain table design where the order of rows matters. This could be another reason why your data are difficult to process by any generic tool. Are your data collected from human readable sources such as websites?

If you think that performance is your main problem and Excel is not an option, try Gnumeric from http://gnumeric.org. It's my favourite spreadsheet application.

Rideaujrk, yes it involves both text and numbers.

The important question is: Does it involve both text and numbers within the same field? This would be the first problem to be solved.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27306
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Speeding up 'Fill Down' function

Postby colwyn » Tue Jul 29, 2008 5:36 pm

Villeroy, I've downloaded gnumeric and input the formula =sum(E9+E10) and the cell shows the formula and not the result ????


You say: "The important question is: Does it involve both text and numbers within the same field? This would be the first problem to be solved."


Whilst text and numbers feature across the spreadsheet, individual columns contain ONLY text OR numbers
Is this helpful?
Colwyn.
OOo 3.0.X on Ms Windows XP
colwyn
 
Posts: 66
Joined: Tue Jul 01, 2008 9:53 pm
Location: Wales

Re: Speeding up 'Fill Down' function

Postby Villeroy » Tue Jul 29, 2008 7:40 pm

colwyn wrote:Villeroy, I've downloaded gnumeric and input the formula =sum(E9+E10) and the cell shows the formula and not the result ????


You say: "The important question is: Does it involve both text and numbers within the same field? This would be the first problem to be solved."


Whilst text and numbers feature across the spreadsheet, individual columns contain ONLY text OR numbers
Is this helpful?
Colwyn.

Like other spreadsheets Gnumeric has an option to show all formulas: menu:Format>Sheet>Show Formulas [Ctrl +].

I believe it's English version uses comma as list separator rather than Calc's semicolon. My German Gnumeric uses semicolon since we use comma as decimal separator. Gnumeric can import ODF to some extent. Just load your Calc file and see.
Yes, database-like lists need to be consistent in order to filter, aggregate and sort correctly.
I have written a Calc add-on for this purpose among many others: http://ooomacros.org/user.php#221020
Gnumeric has something similar in it's find&replace tool.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27306
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Speeding up 'Fill Down' function

Postby jrkrideau » Wed Jul 30, 2008 3:47 pm

colwyn wrote:Villeroy, I've downloaded gnumeric and input the formula =sum(E9+E10) and the cell shows the formula and not the result ????


You say: "The important question is: Does it involve both text and numbers within the same field? This would be the first problem to be solved."


Whilst text and numbers feature across the spreadsheet, individual columns contain ONLY text OR numbers
Is this helpful?
Colwyn.


Another question is what do you call text? To me text is a set of words. lets say the column contains a list of names
    James
    Tin
    Harry
    Sue
    Abdullah
    Eric

This to be is text. A column with formulae would be something like
[list=]
SUM(D1;D4)
SUM(D1;D4)
MOD(C1;1)
MOD(C1;1)[/list]

Which do you have?
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3706
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Speeding up 'Fill Down' function

Postby colwyn » Wed Jul 30, 2008 8:04 pm

Rideaujrk,thanks, I have columns whose cells contain only text - columns whose cells contain only numbers - and columns whose cells contain formulas that produce text in those columns - and columns whose cells contain formulas that produce numbers in those columns.

Another problem I've hit is that when I try to fill down formulas the computer freezes and I have to reboot. This happens in Excel, Openoffice and Gnumeric.
Colwyn.
OOo 3.0.X on Ms Windows XP
colwyn
 
Posts: 66
Joined: Tue Jul 01, 2008 9:53 pm
Location: Wales

Re: Speeding up 'Fill Down' function

Postby jrkrideau » Thu Jul 31, 2008 4:07 pm

colwyn wrote:Rideaujrk,thanks, I have columns whose cells contain only text - columns whose cells contain only numbers - and columns whose cells contain formulas that produce text in those columns - and columns whose cells contain formulas that produce numbers in those columns.


Okay, got it. The more I hear about this data, etc, the more I am inclined to think that you really need to switch to a data base or a stats package. That way you can split the data from the operations and reduce the size of your problem. Operations that require equations in each row of a column to match the data in another column can usually be replaced with a one-line command.

For example if I wanted to do the equivalent of a fill-down command to fill a complete column with random numbers in R (the stats program I normally use) I would need a one-line command { aa <- rnorm(65,536)} and to multiple the equivalent of the column by 2 becomes {2*aa} . Data bases offer the same kind of thing

There is definately a learning curve for something like this but give the large amounts of data and formulae you're dealing with, I suspect that you'll save a lot of time in the long run.

What exactly are you doing with this data etc? The final output may help us suggest other approaches to what you are currently trying to do.


Another problem I've hit is that when I try to fill down formulas the computer freezes and I have to reboot. This happens in Excel, Openoffice and Gnumeric.
Colwyn.


I'm just guessing here but is it possible that you're running into a physical memory problem here?

I don't think the same problem should be showing up in all three spreadsheets as a software problem
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3706
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Speeding up 'Fill Down' function

Postby colwyn » Thu Jul 31, 2008 11:32 pm

I also wondered if it was a hardware problem. I have 1gb ram and 80gb hard drive with AMD 2600+ processor. Thought this should have been sufficient.
OOo 3.0.X on Ms Windows XP
colwyn
 
Posts: 66
Joined: Tue Jul 01, 2008 9:53 pm
Location: Wales

Re: Speeding up 'Fill Down' function

Postby jrkrideau » Fri Aug 01, 2008 3:19 pm

colwyn wrote:I also wondered if it was a hardware problem. I have 1gb ram and 80gb hard drive with AMD 2600+ processor. Thought this should have been sufficient.


Well 1 GB sounds like a lot but who knows? You might try making sure that nothing is running but OOo and see what happens.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3706
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada


Return to Calc

Who is online

Users browsing this forum: Majestic-12 [Bot] and 26 guests