+ Reply to Thread
Results 1 to 10 of 10

array alternatives

  1. #1
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    array alternatives

    now i'm not a fan of arrays and always try to find an alternative here are 3 alternatives to 3 things normally seen done with array entered formulas
    any more that can be added? or any improvements(apart from an array alternative of course) or is there anything plain wrong?
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,659

    Re: array alternatives

    Aside from adding a function (INDEX) to avoid the necessity of CSE, I'm not sure I see the point. I wouldn't expect they calculate any faster. What am I missing?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: array alternatives

    @martindwilson

    Or same example but with pivot table (added in the file).
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: array alternatives

    well i think they are easier! i can remember them and reconstruct them at a drop of a hat,plus you often see wonderful constructs of things like ROW(A27:A50)-ROWs(A1:a26)which i never see the point off

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,659

    Re: array alternatives

    Ah. OK .

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: array alternatives

    but ive just timed them all and the arrays tend to be marginally faster than the equivalent index construct so maybe the gain is worth it however its a very small difference .for example the
    lookup on two values over 7000 rows is array average of 0.001826 s and the index combo is 0.002089s

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: array alternatives

    Hello Martin,

    For a 2 criteria lookup you can use LOOKUP function like this

    =LOOKUP(2,1/($B2:$B25=$K1)/($A2:$A25=L1),$C2:$C25)

    LOOKUP, like SUMPRODUCT typically doesn't require CSE. That will get the same results as your version given your example values - although where there are multiple matches it returns the last one rather than the first...

    The MAX(IF version could conceivably return an incorrect result if the max value is < 0 (it'll return zero) and of course MIN(IF isn't possible for similar reasons.....
    Audere est facere

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: array alternatives

    But you could have used in F9:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



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

    Re: array alternatives

    I like this idea in the multi lookup of LARGE with 1/ROW() as opposed to SMALL with ROW() to get round the zero issue. One to remember.

    Still probably going to use an array version, though!

    Cheers
    Click * below if this answer helped

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

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: array alternatives

    @snb reason i dont use rows($1:1) or row($1:$25) is f4 doesn't work on those and i'm no typist.

+ 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. ADO too slow, any alternatives?
    By CrashAlpha in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-20-2013, 08:52 PM
  2. Looking for Alternatives... Use nested IF AND? Or LOOKUP? Or ARRAY?
    By lisach in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-18-2012, 01:33 PM
  3. Faster alternatives to array formulas
    By Spellbound in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-10-2012, 08:37 PM
  4. What are the alternatives ???
    By christopherp in forum Excel General
    Replies: 4
    Last Post: 03-19-2006, 10:39 AM
  5. Alternatives to Excel
    By Spider in forum Excel General
    Replies: 2
    Last Post: 03-01-2005, 09:06 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