Indirect function and processing power.

[Eamonn100]

I was wondering which of these two indirect formulas (that preform the same calculation) would use less processing power and not slow Excel down as much? Looks can be deceiving so I thought I'd ask those who would know for sure.



=INDIRECT($A$1&CELL("address",$b3))

=INDIRECT($A$1&ADDRESS(ROW($b3),COLUMN($b3))))

 

[cerfani]

The only difference I can tell is you look up the address in one using a string key and the 2nd one you grab the values with row and column function (should be faster than a table lookup) and then address function creates the address using concatenation internally (guessing).

So to me the only difference is concatenating a string (converting 2 numbers to chars and building a new string array) or looking up the address in a table. I would assume doing the lookup is more efficient (the first one). I am totally guessing.

You should create a benchmark test. Use a stopwatch and call each one a thousand times and time it.

 

[Jonmo1]

I would say once INDIRECT has been introduced, the difference between using Cell and Address will be insignificant.
INDIRECT is the real performance hog in those formulas.

 

[Eamonn100]

Yeah I've read that Indirect is a drain but any saving between Cell and Address will help as at times I've got 20,000,000+ Indirect formulas.

 

[Jonmo1]

20 MILLION INDIRECT functions?

That's the problem. Not Cell or Address.

 

[Eamonn100]

@cerfani

That's a good idea. I'll just do that tomorrow.

 

[Eamonn100]

20 MILLION INDIRECT functions?

That's the problem. Not Cell or Address.

Yeah, I'm trying to make it as efficient as possible. With those numbers any small saving on one formula will add up.

 

[Aladin Akyurek]

CELL is probably better than ADDRESS+ROW+COLUMN. The trouble is that you invoke INDIRECT a terrible number of times, causing big recalc times.

 

[Andrew Fergus]

Do you need to use the INDIRECT function? Can you explain what you are wanting to achieve?

 

[Eamonn100]

CELL is probably better than ADDRESS+ROW+COLUMN. The trouble is that you invoke INDIRECT a terrible number of times, causing big recalc times.

Well I did a test and the Cell way completes in 2rds the time of the ADDRESS+ROW+COLUMN way.
.