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