• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Which Index format is more efficient?

dronka

dronka

Member
I'm having some performance issues with a workbook I created. There are some significant data tables (the largest is 38,000 records), and I use a lot of Index functions. I'm having performance problems (the spreadsheet stops to process every few minutes, and it can take up to a minute to process). All of my Index functions are formatted like this example:

=INDEX(Table_Lateral[[#All],[Fiscal Year]],MATCH(Table_Chambers[[#This Row],[Last Name]],Table_Lateral[[#All],[Last Name]],0))

I recently discovered that you can represent this same function this way:

=INDEX(Table_Lateral[Fiscal Year],MATCH(Table_Chambers[[#This Row],[Last Name]],Table_Lateral[Last Name],0))

Which function is more efficient (I assume the latter)? And will changing all of my Index functions to the second option have a significant impact on performance?
 
Hi David ,

I do not know which of the two versions you have given is more efficient ; however , I have a question of my own - what are the operations ( actions ) you carry out on the spreadsheet which causes the spreadsheet to recalculate ? Can you give this detail ?

Secondly , irrespective of the kind of formula you choose , using a helper column can almost always help ; if you can see which are the static elements of your formulae which will not change frequently , and see if these can be isolated from the formulae , in the form of one or more helper columns , not only will this simplify your formulae , it might also speed up the spreadsheet.

Narayan
 
Narayan - Thanks for the quick reply! In regards to your first question, is there an easy way for me to find out which operations are recalculating?

Good point about the helper column. Just to be sure -- you're suggesting that if there is a formula that uses multiple steps, that I separate those steps into separate columns and then combine them in the final column with a simpler function that pulls from the helper columns?
 
These links were super helpful, Narayan. I turned the processing option to manual processing, and that immediately made the workbook easier to work in. I may purchase the FastExcel and see if it can help optimize my workbook. Have you ever used any of their products?

David
 
There may be other issues or ways to resolve the speed

Can you post your file for us to review ?
 
You must log in or register to reply here.
Back
Top