+ Reply to Thread
Results 1 to 7 of 7

Formula Optimization

  1. #1
    Registered User
    Join Date
    06-15-2016
    Location
    BC
    MS-Off Ver
    excel 2013
    Posts
    52

    Formula Optimization

    Hello,

    I am using an array formula to generate a unique list, which looks like: {=INDEX(Sheet1!$A$3:$A$3010, MATCH(0, COUNTIF($B$3:B48, Sheet1!$A$3:$A$3010), 0))}
    The issue though is that it causes some lag when editing the cells it is referencing.

    Is there another way to write this so that it doesn't add latency when inputting data?

    Thank you,
    Kanuck

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula Optimization

    Typically, I remove array formulas from my dashboards by adding columns to my raw data to do the base calculations for me row by row using normal non-volatile functions. Then on my dashboard I can use simpler SUM formulas or MATCH formulas against the added data columns.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula Optimization

    This formula is more efficient.

    Data Range
    A
    B
    C
    1
    All Data
    ------
    Uniques
    2
    A
    A
    3
    A
    D
    4
    A
    S
    5
    D
    W
    6
    D
    X
    7
    S
    8
    W
    9
    X
    10
    X


    This array formula** entered in C2:

    =IFERROR(INDEX(A:A,SMALL(IF(FREQUENCY(MATCH(A$2:A$10,A$2:A$10,0),ROW(A$2:A$10)-ROW(A$2)+1),ROW(A$2:A$10)),ROWS($C$1:C1))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit Enter.

    Copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    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: Formula Optimization

    You can avoid the use of array functions by means of a helper column. Using Tony's example layout, put this formula in B2:

    =IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"")

    then copy down to the bottom of your list. Then you can use this formula in C2:

    =IFERROR(INDEX(A:A,MATCH(ROWS($1:1),B:B,0)),"")

    and copy this down until you get blanks. This will execute far more quickly than your array formula covering over 3000 names in column A.

    Hope this helps.

    Pete

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula Optimization

    Quote Originally Posted by Pete_UK View Post
    =IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"")
    If you copy that formula down 3000 rows this is how many cells will be evaluated:

    =SUMPRODUCT(ROW(A1:A3000))*2


  6. #6
    Registered User
    Join Date
    06-15-2016
    Location
    BC
    MS-Off Ver
    excel 2013
    Posts
    52

    Re: Formula Optimization

    Thank you guys for the help.
    @Pete_UK that works great, thank you for outlining that.

  7. #7
    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: Formula Optimization

    You're welcome - thanks for the rep.

    Pete

+ 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] SUMPRODUCT formula optimization for multiple criteria addition
    By Strazdas in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2015, 03:01 AM
  2. Optimization problem with POISSON formula
    By konesmeijer in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2015, 04:06 PM
  3. Formula Optimization for Combining Matrixes
    By par0016 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-20-2013, 03:23 AM
  4. Formula Optimization on several Countifs formulas
    By dcgrove in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-18-2013, 02:31 PM
  5. Sumproduct formula optimization
    By sinspawn56 in forum Excel General
    Replies: 4
    Last Post: 11-06-2012, 02:23 PM
  6. looking for bar optimization formula for Excel
    By seaflyer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2010, 09:37 AM
  7. optimization
    By kckar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-09-2005, 01:05 PM

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