# Speed issues

1. ## Speed issues

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

2. ## Re: Speed issues

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" <Antonio@discussions.microsoft.com> wrote in message news:3050F1E0-94F6-4E5E-8C29-A26B46828050@microsoft.com...
| 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

3. ## Re: Speed issues

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" <Antonio@discussions.microsoft.com> wrote in message news:3050F1E0-94F6-4E5E-8C29-A26B46828050@microsoft.com...
> | 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
>
>
>

4. ## Re: Speed issues

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" <Antonio@discussions.microsoft.com> wrote in message news:861AC62E-95E2-4CD9-BB13-CADDB9D728A9@microsoft.com...
| 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" <Antonio@discussions.microsoft.com> wrote in message news:3050F1E0-94F6-4E5E-8C29-A26B46828050@microsoft.com...
| > | 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
| >
| >
| >

5. ## Re: Speed issues

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" <Antonio@discussions.microsoft.com> wrote in message news:861AC62E-95E2-4CD9-BB13-CADDB9D728A9@microsoft.com...
> | 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" <Antonio@discussions.microsoft.com> wrote in message news:3050F1E0-94F6-4E5E-8C29-A26B46828050@microsoft.com...
> | > | 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
> | >
> | >
> | >
>
>
>

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1