+ Reply to Thread
Results 1 to 5 of 5

Speed issues

  1. #1
    Antonio
    Guest

    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. #2
    Niek Otten
    Guest

    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. #3
    Antonio
    Guest

    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. #4
    Niek Otten
    Guest

    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. #5
    Antonio
    Guest

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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