Index vs Offset Performance Considerations

[Uncle Ed]

I'm trying to determine the best functions to use based upon performance considerations. I have been using a ton of Index w/Match and have noticed my file size of my workbook is getting quite large. I thinking of using the Offset w/Match instead in hopes that might reduce the file size.

I don't want to build my model using one method and end up with a 30MB file that takes 2 minutes to open and save if I can end up with a 5MB file using another method.

Any input on the merits of using these two functions in terms of processing speed, file size, and flexibility would be most appreciated.

 

[tusharm]

Of course, I don't really know what you are doing, but when I see a post about an XL file 30 MB large "using a ton of Index w/Match" my instinctive reaction is that this person

• (a) is using XL for a purpose it was not designed for,
  (b) is not fully exploiting XL's very powerful capabilities such as its ability to operate on entire ranges at one go,
  (c) is doing in XL what is better supported with VBA functions/subroutines, or
  (d) has an application that is already fine-tuned and I should keep my comments to myself.

Optimizing one function as being faster/smaller than another might -- and I emphasize might -- help at the margins. While I could be wrong, it won't lead to any dramatic improvements in performance.

You might be better off sharing what you are doing -- the business (or other scientific or whatever) intent, not the implementation -- and seeing if someone can suggest structural improvements.

 

[PaddyD]

Further to Tushar's points, in general I'd expect offset to perform worse than index. Offset is volatile & recals on every sheet calculation, not only when its precedents change etc. However, as Tushar suggested, without knowing more about what you're doing it's very hard to say anything specific. (For example offset() may compare favourably to an index with 2 embedded match() functions).

More details please...

 

[SydneyGeek]

I agree with Paddy. I have done some comparisons using an add-in called FastExcel, and it show that (with my data, anyway) INDEX is about twice as quick per cell as OFFSET. Add to that the volatility of OFFSET, and if you have a really large workbook, you may well find that INDEX is quicker.
However, Tushar also makes the good point that you may be asking too much of Excel, and possibly a database would be the way to go.

Denis

 

[Uncle Ed]

Model Rebuild_Old2.xls
	A	B	C	D	E	F	G	H	I	J	K
2	Forecast								0.7	0.8	0.9
3									04/01/02	07/01/02	10/01/02
4									06/30/02	09/30/02	12/31/02
5
6	Desc	Date	Total	Cash	Debt	TP	TD	InAct	2Q02	3Q02	4Q02
7	PhaseI	6/1/2002	10,000	8,900	1,000	0.7	29		10,000	-	-
8	PhaseII	8/1/2002	20,000	17,800	2,000	0.8	60		-	20,000	-
9	PhaseIII	10/1/2002	30,000	26,700	3,000	0.9	91		-	-	30,000
10
11	Desc	Date	Total	Cash	Debt	TP	TD	InAct	2Q02	3Q02	4Q02
12	PhaseI	6/1/2002	10,000	8,900	1,000	0.7	29	-	1,000	-	-
13	PhaseII	8/1/2002	20,000	17,800	2,000	0.8	60	-	-	2,000	-
14	PhaseIII	10/1/2002	30,000	26,700	3,000	0.9	91	-	-	-	3,000
TR

Ok, here is an example: My model basically has a transaction section (top range) that details each individual transactions and TOTAL in the respective time period across the top of the sheet.

There are about 15 sections that follow the transaction section and perform different calculation (such as interest expense, depreciation, etc...) and also places these in the respective time periods. All of these results then flow into Income Statement/Cash Flow documents by Quarter so I can project out.

I calc which time period ("TP") for the transaction using the Offset and Match functions and calc the number of days from the transaction date to the end of the quarter using the Index and Match functions.

Basically what I'm wondering is since I'm doing these calculations for each of the 15 sections, will there be any perfomance considerations or flexibility considerations in using Offset/Match vs Index/Match.

I'm just getting started using VBA, so until I get more fluent, I need a straight XL solution.

Any advice is appreciated. I'm rebuiling my model and want to implement the most efficient and flexible formulas.

Super thanks in advance for any ideas. This board is awesome!!!!!

ue

 

[tusharm]

So, where do you have the 'ton of Index/Match' formulas?

Each of the 15 sections is how large? 5 rows by 10 columns?

Do you by any chance do a lot of inserts and deletes? What happens if you press CTRL+END? Where does XL take you? Is it really the logical last row / column of your worksheet?

How many worksheets do you have in the workbook?

{snip}
Ok, here is an example: My model basically has a transaction section (top range) that details each individual transactions and TOTAL in the respective time period across the top of the sheet.

There are about 15 sections that follow the transaction section and perform different calculation (such as interest expense, depreciation, etc...) and also places these in the respective time periods. All of these results then flow into Income Statement/Cash Flow documents by Quarter so I can project out.

I calc which time period ("TP") for the transaction using the Offset and Match functions and calc the number of days from the transaction date to the end of the quarter using the Index and Match functions.

Basically what I'm wondering is since I'm doing these calculations for each of the 15 sections, will there be any perfomance considerations or flexibility considerations in using Offset/Match vs Index/Match.

I'm just getting started using VBA, so until I get more fluent, I need a straight XL solution.

Any advice is appreciated. I'm rebuiling my model and want to implement the most efficient and flexible formulas.

Super thanks in advance for any ideas. This board is awesome!!!!!

ue

 

[Uncle Ed]

So, where do you have the 'ton of Index/Match' formulas?
*** Currently, they are mainly in the TP and TD columns ***
Each of the 15 sections is how large? 5 rows by 10 columns?
*** Each section is about 200 rows and about 35 columns wide (incl. periods from 2002 through 2007) ***

Do you by any chance do a lot of inserts and deletes? What happens if you press CTRL+END? Where does XL take you? Is it really the logical last row / column of your worksheet?
*** I do a fair number of inserts and deletes from the first section...one issue I have is if I need to add a row to the 1st section, I must add a row to all the other sections as these all feed from the previous section (is there an easier way to handle this?) CTRL+END does take me to the logical last row/column (approx. AM2800)***

How many worksheets do you have in the workbook? "
*** Around 20 Worksheets, but this is the largest one and most of the others feed off this sheet ***

*** One other note, I'm calculating TP and TD so I don't need to calculate in each cell of the time periods for each section ***

thanks
ue