Formula Sheet

[lamkoid]

Hi everybody!

I’m trying to populate a very large spreadsheet with formulae. It is 10k rows by 10k columns, which works out to 100 million cells. Fortunately, the formulae only cover the lower diagonal half, so there are a mere 50 million of them. I have attached an excel file with the first 10 diagonal formulae and some vertical fill down to illustrate the progression of the formulae. In the final version, all the columns will be filled down to the 10k row.

The first row is an index that runs up to 10k that may (or may not) be useful, and the first column is dummy data that also acts as an index.

It seems to me that some sort of macro would be called for, but my excel skills are not very sophisticated and I tend to think that macros are always the best solutions for everything.

Any help would be greatly appreciated.

[millz]

Not sure how long would this take:

Please Login or Register  to view this content.

[FDibbins]

wow with that many cells, perhaps excel is not the tool for this job?

How would you find anything?

[lamkoid]

Thanks millz!

That macro produced exactly the correct results, up to a point.

I tested it at 1k and it took about 2 minutes to fill the sheet. Then I ran it at the full 10k and after about 30 minutes I got a run-time error. The macro had only filled out to about 2600 columns when it stopped.

I changed the calculation options from automatic to manual to see if that would help, and it did speed things up. The 1k test went from about 2 minutes to a few seconds, and the 10k version went from 30 minutes to about 3 minutes.

Unfortunately the runtime error still occurred, and at about the same point: around column 2600.

The error reads:

Run-time error ‘1004”:
FillDown Method of Range Class Has Failed

Any ideas on how to work around that?

[lamkoid]

Message Deleted

[lamkoid]

wow with that many cells, perhaps excel is not the tool for this job?

How would you find anything?

Re: Formula Sheet

LOL,

That's an interesting perspective; it never occurred to me that someone would think that.

It turns out that I am using conditional formatting to identify patterns in the output results.

I had been using a more inefficient version of the formula in a spreadsheet and was running up against calculation limitations in Excel at around 66k x 66k arrays, even with my high end i7 processor and gobs of Ram.

From my tests with millz's macro results, it looks like calculation limitations should not be a problem, if I can assemble the new efficient formula in the sheet.

[millz]

It appears that Excel do not have enough memory to perform the "auto fill down" of formulae all at once. I tested on mine and it failed at column 2977. Never seen this error before as I have never dealt with such a huge matrix.

Not tested, but I suppose you might be able to split the task into 1000 columns at a time. Run for 1000 columns, save, close, re-open, edit macro for the next thousand, save, close, ...repeat.

[FDibbins]

Using CF - with any formula or simple rule - on that many cells will for sure drag your file down to a crawl. Cf can become very resource-intense

[lamkoid]

Using CF - with any formula or simple rule - on that many cells will for sure drag your file down to a crawl. Cf can become very resource-intense

Interesting point. I've been using the conditional formatting with my inefficient current version which has about 22 million active cells. It takes a few minutes to grind through a set of data, but that is not a problem for my purposes. Although the CF drag may increase exponentially as the array size increases from 22 million to 50 million active cells.

For this new sheet I'm trying to assemble, I haven't yet applied the CF, so that's not causing the run-time errors.