Absolute Referencing Problem

in H2
=INDEX($B$2:$E$4,QUOTIENT(ROWS($1:1)-1,3)+1,MOD(ROWS($1:1),3)+(MOD(ROWS($1:1),3)=0)*4)
and copy down

Or this

=IFERROR(INDEX($B$2:$E$4,INT(ROWS(A$1:A3)/3),INDEX({1,2,4},MOD(ROWS(A$1:A3),3)+1)),"")

I am sorry to hear that. Somehow I just overlooked the part of your post were it says 65,000 rows and went straight for the spreadsheet. IMO, the formulas that were provided for this task all use functions that are least resource-hungry but now we all can agree that 65,000 rows is two much for any formula. Just ran a test on the three columns and 70,000 and 15 min later still waiting on my i3.
I think a better option would be to pursue a VBA solution.

Originally Posted by britzer

Yes I would believe so. I tried to stay away from VBA because i want the sheets to be as "understandable" as possible so a non experienced user
can change referencing is needed. It has to be flexible, but VBA with instructions would do i guess.

Can you help me with a Macro to get started, please?

I did post on the board a request for VBA solution so hopefully someone will jump in to help.

http://www.excelforum.com/the-water-...ml#post4010743

Does the entire formula need to be in a single cell? I have often observed that some of these "computationally expensive" formulas like this are intensive because we have tried to do the entire formula in a single cell. I think, in many cases, this leads to "duplication of effort" -- each incidence of the formula needs to repeat what the previous incidence of the formula did. Multiply that duplication of effort, and you get processor intensive spreadsheets.

The way I would tend to do something like this:

1) Have a column that computes the desired column #. This will usually be something like H3=IF(H2=4,1,H2+1), then copy down. Enter 1 in H2.
2) Have a column that computes the desired row #. This will usually be something like G3=IF(H2=4,G2+1,G2), then copy down. Enter 1 in G2. You can tie these into the "repeat number" if desired.

These two columns will then feed into the "row" and "column" arguments of an INDEX() function. I2=INDEX($B$2:$E$40000,H2,G2) copied down.

Testing this on a 40000 row spreadsheet, and calculation was near instantaneous. Testing on an 80k row spreadsheet, and the calculation event was a couple of seconds. Extending this formula down to near 400k rows (which would capture 120k+ rows of source data) took about 8 seconds.

Anyway, I guess my point is, if you are willing to separate the computations into multiple simple columns, you may be able to dramatically improve performance without resorting to VBA.

Simple macro in your file:

Please Login or Register  to view this content.