Macro to run formulas more efficiently

Dear Gurus,

I generally analyze data by applying formulas. However when the data I need to analyze is too big (500 columns and 60000 rows), calculating all the formulas takes excel several hours, if not days. This slows the performance of my computer dramatically and I would like to know if it is possible to have a macro run the formulas on a step wise manner and in a much more time efficient way. In the attached file you can see an example of the data and how the formulas are applied in a step wise manner. Let me know if you would like me to clarify further.

Many thanks in advance for your help,
Ale

Hi Ford, thanks for answering. I am sorry is confusing. I erroneously attached a bigger example file with irrelevant information. I would try to be more specific now. I am attaching a new file.

The first thing I would like the macro to do would be to apply the 1st formula, and generate the results on cells J4 - N13 (you can see the formula working on all these cells). This 1st formula finds all the "forward" overlapping introns. Basically for every intronID on column G, it looks if the intron in the row below has a start (column H) that is bigger or equal to the stop (column I) of the IntronID under inquiry. If this is true, then the formula gives me "." on column J, if false, I get the IntronID of the overlapping intron on cell J4. This formula can be copied down without modifications on column J. I also need to know if the overlapping occurs between the IntronID under inquiry and +n IntronIDs below (columns K - N). This means that when copying the formula to the right (from column J to columns K - N), for each column where I add the formula, I need to add +1 to the H and G values. For example see that the formula is "=IF($H5>=$I4,".",$G5) " in column J and changes to "=IF($H6>=$I4,".",$G6) " in column K and to " =IF($H7>=$I4,".",$G7) " in column L, successively until column N. Columns are added after J, until all the values in 2 following columns are "." . You can see how column M and N all have "." values.

The second part of the macro should find all the reciprocal overlaps of all introns. For this I use a combination of a IFERROR(VLOOKUP) formula. First I copy the list of IntronIDs to column O. Then I ask if the IntronID on cell O4 is found in the array J4 - O13, then give me the IntronID of the last column in the array, or cell O4. If is not present in the array, then give me "." . Again the formula can be copied down without modifications, but when copied to the right, it needs to shift the location of the array one column to the right. So, a formula on P4 will start in column J " =IFERROR(VLOOKUP($O4,J$5:$O$13,6,FALSE),".") " , a formula in Q4 will start on column K =IFERROR(VLOOKUP($O4,K$5:$O$13,6,FALSE),".") and so on for the same amount of columns as for the first formula.

Column U is to check if formulas 1 and 2 gave all the correct results. This column can be ignored by the macro and doesn't need to be generated.

Once all the Intron Overlaps are calculated (both forward and reciprocal), the macro needs to retrieve the values of the overlaps for each experiment on cells B4 - E13. This is done again with a combination IFERROR(VLOOKUP) formula. It asks if the intronID generated on the overlap columns is present in the array A5 - E13, then give me the value for a particular experiment. So for example for the values of the overlaps for the Experiment 1 (columns V - AE), formulas on column V ask for intron overlaps in column J. Formulas on column W ask for intron overlaps on column K and so on. Notice that when all the formulas for Experiment 1 are true, they retrieve the values of the col_index_num = 2, or the second column of the array, these are the Experiment 1 values. Formulas for other Experiments (2, 3 and Total) use the same formula but need to specify the column change for the particular experiment.

Many thanks again!
Ale

I would like a macro to give the same results you see displayed on columns J to BI, by implementing all the different formulas in the way I described above. Assume that columns J - BI are empty and the macro will fill them out. Since this is doable with formulas on a small scale I thought it could be much faster using a macro that implements all the different formulas.

I guess you are asking if there is only one specific formula that can be applied to all the data and give me the results I expect, but the truth is that the formulas vary depending on the conditions explained above, they all need to be modified in a certain way. I was trying to give as many explanations to the different conditions in my second reply. Thanks anyways for trying to understand.