Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    When to use array formulas?

    If you have an Excel list of several thousand rows where column B contains formulas such as =LEFT(A2,3), you could handle it in one of two ways.
    1. (the easy way) enter =LEFT(A2,3) in cell B2 and copy it down to all the cells below, or
    2. select cells B2:B3000 (or whatever) and enter the array formula {=LEFT(A2:A3000,3)}.

    I would have thought that #2 would use less memory, calculate quicker and result in a smaller file size. The downsides would be the reduced maintainability (the "average" Excel user has never heard of array formulas), and the fact that you can't insert rows - particularly annoying if you have different array formulas in columns C, D, etc. as well. You would have to get rid of the array formulas, insert the row(s), then recreate the array formulas.
    Anyway, regarding the possible benefits listed above, from what I can tell:
    (a) the array formulas use MORE memory;
    ( the array formulas calculate SLOWER; and
    © the file size is only slightly smaller - maybe by 5%.

    My conclusion at this point would therefore be that array formulas should only be used when they have to be.

    Any thoughts?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: When to use array formulas?

    My conclusion is the same. Arrays are very useful, but their recalc time impact can be such that I avoid them for calculations with a very large number of cells used in the formula argument (either a big range or many ranges). For my needs I often go with a multiple step =sumif(), and I've buried my P111 500 for twelve minute recalcs with cascaded sumif's!
    -John ... I float in liquid gardens
    UTC -7±DS

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When to use array formulas?

    True, you can't insert rows in the middle of the array, but there are cases where array formulas are invaluable. For example, I often have to use a 'SumProductIF' construct to calculate average salaries by month,by dept. from a long title/salary/dept listing which could have multiple counts for each title changing by month. I use an array formula construction like:
    =SUM(IF(dept=something,(Month#headcount*salary)/month#headcount,0)

    Once this is constructed, I can insert or delete rows withing the listing without breaking the formulas. I only have to work with a thousand rows or less so performance is no big factor.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When to use array formulas?

    Hi,

    I'd definitely agree with the timing degradation. I had a situation where I had perhaps 200-300 array formulae on large vvolumes of data- they were taking several minutes for a recalc. I ended up removing the formula and doing everything through VBA- it takes 2 seconds now.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •