Thank you Niek for the link, it is quite helpful. Basically what I was
looking for.

Antonio

"Niek Otten" wrote:

Hi Antonio,

<I could also use: =SUM(U5:OFFSET(U5,N_NAMES,0)) (where N_NAMES='EQ INV'!E1)

No. You would have to use the INDIRECT() function

<or even =SUM(U5:$U$20000)

That would be lightning fast. Do that and forget about optimizing. You'll never match the speed of these intrinsic Excel
functions.

Why don't you set up a test sheet with (say) 10,000 entries, try the different methods, time them AND CHECK RESULTS for
correctness?

Very good info on performance can be found on Charles William's site:

www.decisionmodels.com


--
Kind regards,

Niek Otten


"Antonio" wrote in message ...
| For example, I am using the following formula:
|
| =SUM(U5:OFFSET(U5,'EQ INV'!E1,0))
|
| I could also use:
|
| =SUM(U5:OFFSET(U5,N_NAMES,0)) (where N_NAMES='EQ INV'!E1)
|
| or even
|
| =SUM(U5:$U$20000)
|
|
| Since I use a similar expression as the first one in many cells in a large
| spreadsheet I am wondering if it is the fastest.
|
| The workbook calculates slowly and optimisation would be helpful.
|
| Looping via VBA to check execution times is not the same thing because the
| formulas and processes are not equivalent.
|
| Manual testing is a way to check but it requires a lot of time.
|
|
|
| "Niek Otten" wrote:
|
| Hi Antonio,
|
| Impossible to say. Vlookup for 10,000 cells can vary from several minutes to a split second, depending on the value of the 4th
| argument and the value to be looked up.
| Please explain in some more details what problem you're trying to solve.
|
| --
| Kind regards,
|
| Niek Otten
|
|
|
| "Antonio" wrote in message ...
| | What is faster, to refer to a cell via its address (in a different worksheet)
| | or via a name?
| |
| | Also, how fast is the offset function?
| |
| | I can do vlookup, sumif and sum with a range that has the limits determined
| | dynamically with the offset function or using large fixed ranges. Which one
| | is faster?
| |
| | Thanks,
| |
| | Antonio
|
|
|