+ Reply to Thread
Results 1 to 11 of 11

Array Formula too slow in calculation

  1. #1
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Array Formula too slow in calculation

    hi,

    Below is the array formula which is taking too much time on calculation process. Is there any other formula which can perform faster.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Array Formula too slow in calculation

    Don't use full-column references like $D:$D in array formulae, as every cell in that range will be examined (as well as E:E, F:F, and G:G). Make your ranges just as large as required.

    You could also think about using SUMIFS if the value you are looking for in column G is numeric.

    Hope this helps.

  3. #3
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Array Formula too slow in calculation

    Thanks, I will try with required ranges.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Array Formula too slow in calculation

    As Pete says, don't use full column references in Array Formulae, ever.

    Try it this way:

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Array Formula too slow in calculation

    This puts the Formula to Value conversion back in:

    Please Login or Register  to view this content.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Array Formula too slow in calculation

    Hi Trevor,

    it is not made clear, but it might be that the formulae are placed in a different sheet than Daily_Summary (as they use B2= ... , N2= ...etc.), so LastRow might be different on the two sheets.

    Pete

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Array Formula too slow in calculation

    Good thought Pete.

    Maybe this then:

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Array Formula too slow in calculation

    Why limit yourself to column A? Since EVERY nonblank cell is in UsedRange, LastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1 would be the bottommost row in use in any column in UsedRange.

    That said, if this is going to be an array formula anyway, make it even more efficient by skipping the multiplication.

    "=IFERROR(INDEX(Daily_Summary!"$G$2:$G$" & LastRow & ",MATCH(TRUE," & _
    "IF(B2=Daily_Summary!$D$2:$D$" & LastRow & "," & _
    "IF(N2=Daily_Summary!$E$2:$E$" & LastRow & "," & _
    "P2=Daily_Summary!$F$2:$F$" & LastRow & ")),0)),0)"

  9. #9
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Array Formula too slow in calculation

    If you're ultimately converting this to values anyway, if the worksheet containing these results and Daily_Summary both have distinct column labels in row 1, you could use MS Query to create an outer join of the 2 ranges as tables. This sort of operation is a table join, so a database approach to performing it may be the most efficient way.

  10. #10
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Array Formula too slow in calculation

    @Pete & TMS

    Thanks, both your suggestion and code works perfectly.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Array Formula too slow in calculation

    You're welcome. Thanks for the rep.

+ 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] Array formula working too slow
    By pezalmendra in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-19-2019, 10:06 AM
  2. [SOLVED] Macro to replace very slow array calculation
    By REV18 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-09-2018, 02:31 AM
  3. [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
  4. [SOLVED] Slow Array formula
    By gassiusmax in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2015, 12:41 PM
  5. [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
  6. Unlink Formula - slow calculation
    By televisi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2011, 05:08 AM
  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