Alternatives to OFFSET - Excel running very slowly

[jacc99]

Hi guys,

I have a very large data set, running well over 1,000,000 offset functions across the sheets. This as you can imagine is making the excel run very slowly.
I have seen index is an alternative to offset in some respects, however i am struggling to apply for my use.
I am currently using offset so that is can dynamically change the lookback period in a returns formula (I have uploaded a small segment from my file, see excel mini below).
I am hoping someone has an idea of how i can implement index or an alternative non volatile function in this respect?

Many thanks,
J

TestSheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1													-1
2	1	Wed 08	1/2/19 8:00	1.36396	8.95442	8.69272	.70031	1.26479	1.14005	.98589	109.162
3	2	Wed 08	1/2/19 8:01	1.36388	8.95093	8.6902	.70036	1.26513	1.14045	.98558	109.151	-5.87E-05
4	3	Wed 08	1/2/19 8:02	1.3641	8.95043	8.69017	.70039	1.26549	1.14054	.98553	109.131	0.000161
5	4	Wed 08	1/2/19 8:03	1.36426	8.95323	8.69416	.70028	1.26474	1.14031	.9856	109.092	0.000117
6	5	Wed 08	1/2/19 8:04	1.36424	8.9528	8.69293	.70017	1.26469	1.1404	.98569	109.086	-1.47E-05
7	6	Wed 08	1/2/19 8:05	1.36397	8.95308	8.69361	.70009	1.26455	1.14036	.98561	109.057	-0.000198
8	7	Wed 08	1/2/19 8:06	1.36383	8.95399	8.69304	.70008	1.26416	1.14019	.98568	109.027	-0.000103
9	8	Wed 08	1/2/19 8:07	1.36365	8.95457	8.693	.70016	1.26411	1.14012	.98561	109.052	-0.000132
10	9	Wed 08	1/2/19 8:08	1.36377	8.95851	8.69557	.70016	1.26396	1.13994	.98571	109.034	8.8E-05
11	10	Wed 08	1/2/19 8:09	1.36385	8.95821	8.69386	.70017	1.26396	1.13999	.98569	109.	5.87E-05
12	11	Wed 08	1/2/19 8:10	1.36341	8.95744	8.69388	.70036	1.26388	1.14019	.98569	109.038	-0.000323
13	12	Wed 08	1/2/19 8:11	1.36369	8.95871	8.69252	.70037	1.26347	1.14003	.98587	109.023	0.000205
14	13	Wed 08	1/2/19 8:12	1.364	8.96117	8.69454	.70031	1.26377	1.13984	.9858	109.017	0.000227
15	14	Wed 08	1/2/19 8:13	1.36406	8.95984	8.69411	.7003	1.26418	1.13989	.98571	109.001	4.4E-05
16	15	Wed 08	1/2/19 8:14	1.36406	8.96144	8.69504	.70032	1.26395	1.13971	.98606	109.017	0
17	16	Wed 08	1/2/19 8:15	1.36394	8.96152	8.69457	.70027	1.26399	1.13974	.9859	109.018	-8.8E-05
18	17	Wed 08	1/2/19 8:16	1.36395	8.96103	8.69399	.70017	1.26425	1.13999	.98599	109.015	7.33E-06
19	18	Wed 08	1/2/19 8:17	1.36349	8.96138	8.69286	.70026	1.26446	1.14016	.98599	109.027	-0.000337
20	19	Wed 08	1/2/19 8:18	1.36367	8.96096	8.69395	.70027	1.26451	1.14024	.98593	109.033	0.000132
21	20	Wed 08	1/2/19 8:19	1.36322	8.96099	8.69465	.70037	1.26453	1.14026	.9857	109.02	-0.00033
Sheet1

Cell Formulas
Range	Formula
M2	M2	=IF(AND(L2="",L2>0),"",IF(L2>0,1,-1))
B2:B21	B2	=TEXT(C2,"ddd hh")
L2:L21	L2	=IFERROR(LN(D2/OFFSET(D2,$M$1,0)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:K21	Cell	contains a blank value	text	NO
C3	Cell	contains a blank value	text	NO
C3	Cell Value	contains """"	text	NO
C3	Cell Value	contains "N/A"	text	NO
H2:K21	Cell	contains a blank value	text	NO
H2:K21	Cell Value	contains """"	text	NO
H2:K21	Cell Value	contains "N/A"	text	NO

 

[Fluff]

How about

=IFERROR(LN(D2/INDEX($D$1:$D$20,ROW(D2)+$M$1)),"")

 

[jacc99]

Amazing! thanks a lot that works perfectly. Really sppreciate the swift repsonce!

 

[Fluff]

You're welcome & thanks for the feedback.