Speed of Indirect or Index

[wildcloud]

I can use indirect or index to randomly retrieve data from a 10,000 long array,

Excel Workbook
	C
6	0.094139914
7	0.136214049
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C6	=INDEX(Sheet4!A1:A10000,RANDBETWEEN(1,10000))
C7	=INDIRECT("Sheet4!A"&RANDBETWEEN(1,10000))

which method will be more efficient? How can I do a benchmark?

Thanks.

 

[Scott Huish]

I don't know how to get a benchmark on it, but I would think INDEX would be more efficient because INDEX is not a volatile function, while INDIRECT is.

 

[Travis]

check out this article if you want to do some time trials with each method

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

 

[DonkeyOte]

Echoing Hotpepper, INDIRECT may evaluate quicker than INDEX (which is I believe quite slow) but given it's Volatile it will invariably be processed far more frequently so long term INDEX would be preferred.

Mind you saying that you're using RANDBETWEEN so it's Volatile anyway...

 

[Aladin Akyurek]

A few runs with FastExcel, averaged gives something like...

With INDEX...

Re Calc: 0.1
Full Calc 0.1
% Volatile: 46%
Microsecs/formula: 144

With INDIRECT...

Re Calc: 0.1
Full Calc 0.2
% Volatile: 44%
Microsecs/formula: 167

This kind of single-formula profiling might not be very useful. The assessment should probably be done like:

Time(Many Formulas)-Time(Many Formulas Except Target Formula)

in order to see how such a formula affects the system.

 

[wildcloud]

Thanks all for the help.