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

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

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

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
|
|
|

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
|
|
|