+ Reply to Thread
Results 1 to 11 of 11

Array formula working too slow

  1. #1
    Forum Contributor
    Join Date
    07-22-2012
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    295

    Array formula working too slow

    Hello,

    I am struggling with a formula, which works OK, but is not efficient for the large amount of data stored in the Excel sheet. The table I am using have thousands of rows and the workbook becomes very slow because of the calculations.

    I was wondering if the formula I am using can be changed or modify to make it quicker. Maybe a macro will do the job as well.

    I have attached a small version of the table where I have the formula. Columns in red have the formulas which I am explaining.

    Hope someone can give me an idea about this. Any help would be much appreciated.

    Thanks & Regards
    Attached Files Attached Files

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,035

    Re: Array formula working too slow

    Try this: =IF(E11=2,(BY11-LOOKUP(2,1/($D$11:$D$1048576=D11)/(BU$11:$BU$1048576=1)/$BY$11:$BY$1048576)/BV11),0)

    Should be faster.
    Click the * to say thanks.

  3. #3
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,861

    Re: Array formula working too slow

    Hi.
    How many thousands of lines do you have ?
    The array appears to be iterating through 1,048,576 lines, is this necessary at this stage of your work.
    In the formula in column 'CA' I have just reduced the three occurrences of this figure to 10,485.
    The calculation is the instant and at 104,857 is less than 1 second.
    torachan.

  4. #4
    Forum Contributor
    Join Date
    07-22-2012
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    295

    Re: Array formula working too slow

    Hi,

    Thanks PaulM100 and torachan for your help!

    I am trying the formula from PaulM100, but is still too slow processing. Excel is not responding when introducing the formula.

    Table has over 350.000 rows

    How did you reduced the formula?

    Thanks & Regards

  5. #5
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Array formula working too slow

    Hi
    I suppose you want this with a helper column
    In column CD try this array formula CD11 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The new values for column CA are given by a normal formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The values in column CB don't need to be array formula.
    See the file
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,861

    Re: Array formula working too slow

    In cell CA11 formula, reduce 1,048,576 to 500,000 (in the three instances) this will adequately cover your 350,000 lines.
    Copy the formula in cell CA11, highlight the range to you are copying down to.
    As you are dealing with an array formula the next step is IMPORTANT !
    Whilst holding down Ctrl & Shift keys together - with cursor in highlighted area - select 'paste'.
    If you have not copied arrays before practice on a copy of your worksheet.
    Also noticed your actual cell values are not the same as your formatted/display values.
    As standard Excel works with the actual cell value, this in turn has effect on speed, especially on older processors.
    You could go to your Excel settings >Options> Advanced> When calc this Workbook >Set precision as displayed to further increase speed.
    torachan.

  7. #7
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,883

    Re: Array formula working too slow

    Kindly delete blank columns.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  8. #8
    Forum Contributor
    Join Date
    07-22-2012
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    295

    Re: Array formula working too slow

    Dear all,

    Thanks for the help, much appreciated!

    I finally used solution from Josť Augusto, works very good! Workbook runs much more smooth.

    I will close this thread.

    Thanks!

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2010
    Posts
    5,284

    Re: Array formula working too slow

    Try this .
    Helper Column CE. In CE11 then copy down.

    =D11&BU11

    I have used Column CF. In CF11 then copy down. This is NOT ARRAY formula ,hence it is faster.

    =IF(E11=2,(BY11-INDEX($BY$11:$BY$1048576,MATCH(D11&1,$CE$11:$CE$1048576,0))/BV11),0)
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  10. #10
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,861

    Re: Array formula working too slow

    Although this thread has been closed by O.P.
    There is a salient lesson in this thread for all of us.
    Congratulation to 'avk' for his observation that we ALL missed.
    After checking this I was amazed at the time Excel took to process nothing when running an array.
    Never toooo old to learn !!!!
    torachan

  11. #11
    Forum Contributor
    Join Date
    07-22-2012
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    295

    Re: Array formula working too slow

    Thanks kvsrinivasamurthy!

+ 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. [SOLVED] Please help Array formula not working
    By lreed in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2017, 06:44 PM
  2. [SOLVED] Excel getting slow with INDEX array formula
    By Lovemyexcel in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 02-19-2017, 11:11 AM
  3. [SOLVED] Slow Array formula
    By gassiusmax in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2015, 12:41 PM
  4. [SOLVED] Array formula too slow. Need an alternative..
    By cool_anu4u in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-28-2013, 04:56 AM
  5. Array formula not working
    By MIAO in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-31-2012, 10:01 AM
  6. Array formula not working - Please Help
    By BS_Watson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-09-2007, 03:05 PM
  7. [SOLVED] MY ARRAY FORMULA IS SLOW IF DATA LIST IS MORE THAN 10000R0WS
    By S.DURAIVEL - ABU DHABI in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2005, 05:10 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