Results 1 to 6 of 6

Dynamic Range Sizes in Formulas

Threaded View

Speshul Dynamic Range Sizes in... 09-12-2014, 09:07 AM
sweep Re: Dynamic Range Sizes in... 09-12-2014, 09:26 AM
Speshul Re: Dynamic Range Sizes in... 09-12-2014, 09:44 AM
oeldere Re: Dynamic Range Sizes in... 09-12-2014, 09:28 AM
hohlick Re: Dynamic Range Sizes in... 09-12-2014, 09:40 AM
Grasmat Re: Dynamic Range Sizes in... 09-15-2014, 09:17 AM
  1. #1
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Dynamic Range Sizes in Formulas

    I had a question on variable length arrays within complicated array formulas, because the data size would change daily, but I couldn't use whole column references because it slowed everything down. While building my sample sheet for upload here, I had an idea and solved my own question. Thought I would share my solution here;

    Scenario: I have values in Column K, starting at K2. I need to SUM them. The number of values is variable.

    Example formula (correct output, slow calculation speed)
    =SUM(K:K)


    Solution:
    =SUM(OFFSET(K2,,,COUNTA(K:K)))



    Note: SUM formula is a placeholder. I know it is not a complicated array function, and I know that this specific example wouldn't be slow enough to need a solution like this. It is just to show how the range is replaced.


    Note 2: This solution assumes no blank rows in Column K.
    Last edited by Speshul; 09-12-2014 at 09:13 AM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Insert cells, fill formulas down, set formulas for dynamic range
    By Snickers65 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 01:43 PM
  2. Dynamic table sizes
    By urngoodhands in forum Excel General
    Replies: 2
    Last Post: 11-23-2012, 08:48 PM
  3. [SOLVED] Formulas' on Dynamic Range
    By knocks420 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-09-2012, 02:34 PM
  4. Adding a dynamic range to formulas
    By sans in forum Excel General
    Replies: 20
    Last Post: 12-07-2011, 10:55 AM
  5. dynamic range of formulas
    By dreamz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2006, 03:53 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