+ Reply to Thread
Results 1 to 3 of 3

Arrey formula taking long time to calculate - any other way or MACRO can be built ??

  1. #1
    Registered User
    Join Date
    09-19-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Arrey formula taking long time to calculate - any other way or MACRO can be built ??

    Hello Everyone,
    i have excel file where i have raw data in "Current" tab and "Previous" Tab copy pasetd from respective original files.
    in Current work tab, i pulled 5 perticulat columns from Current tab in column B-F and same is done in Previous Work tab.
    in column I-L i have some Arrey formula in place which search for the Match condition formula.
    On basis of results in those column i pull the data using Index formula from Previous Work file matching to current work file column B-F.

    Using this arrey formula it is taking too much of time to calculate my excel file (around 3-5 mins) as i have kept the calculation mode as Manuall.

    can anyone help me in reducing this time or providing me simple MACRO which can be RUN once i Copy the data in Current and Previous Tab.

    any help is really appriciated.
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Arrey formula taking long time to calculate - any other way or MACRO can be built ??

    Hi,

    Referencing entire columns in array formulas is not advisable, and more than likely is the main contributing factor to your poor spreadsheet performance.

    Amend 'Previous Work'!$B:$B in your formulas, either with dynamic offsetting or by choosing a sufficiently (though not overly) large upper limit to the range reference.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Arrey formula taking long time to calculate - any other way or MACRO can be built ??

    BTW, crossposted here: http://www.mrexcel.com/forum/excel-q...can-built.html
    Remember what the dormouse said
    Feed your head

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Array Formulas taking too long to calculate
    By CheeksExcelForum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-17-2013, 06:56 PM
  2. Optimizing Formulas - taking too long to calculate large worksheet
    By lightsandsirens in forum Excel General
    Replies: 0
    Last Post: 08-21-2012, 10:38 AM
  3. Excel 2007 : Macro taking a long period of time
    By Nikeyg in forum Excel General
    Replies: 2
    Last Post: 09-07-2011, 07:37 AM
  4. Better way to transfer data my way taking a long time
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-06-2011, 06:03 AM
  5. Macro taking too long time to run
    By sharmanjali87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-02-2010, 07:08 AM

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