October 21, 2004

Dynamic Ranges

Actually, I'm not really sure whether this should be called a Dynamic Range formula but for the time being please bear with me :-)

A while ago, I posted showed a LARGE function array in my post Duped by Duplicates. (Array formulas must be entered by pushing Ctrl, Shift and Enter simultaneously instead of just Enter)

Since then I've been thinking that arrays of this type are rather limited so I've been doing some experimenting.

Let's look at the below picture. In Column B, I have fifteen numbers, and in Columns C, D, E and F I have various formulas to show them from largest to smallest.



Column C
{=LARGE(B2:B16,ROW(INDIRECT("1:15")))}

Column D
{=LARGE(B2:B16,ROW(INDIRECT("1:"&COUNTA(B:B))))}

Column E
{=LARGE(B2:B16,ROW(INDIRECT("1:"&ROWS(B2:B16))))}

Column F
{=LARGE(B:B,ROW(INDIRECT("1:"&COUNTA(B:B))))}

Note that all of these formula work but they have different attributes.

The formula in Column C is simple and straightforward. The formula in Columns D and E have an advantage in that they are easy to input. Just enter them in the same number of rows as Column B and they will adjust automatically, no counting or calculating rows is necessary.

Then we have Column F. Of all four formulas, I would say that this formula uses the most memory but is still quite easy to input once you get used to it.

There's an added advantage. Watch what happens when I drag the range in Column B.



Only the values shown in Column F have changed. And it shows the true top fifteen values in Column B.

Now I try to expand the array formulas in Columns C, D, E and F by selecting (not dragging) the same number of rows as Column B, place the cursor anywhere within the formula as shown in the Formula Bar, then re-entering the array by pressing Shift, Ctrl and Enter.

Here are the results.



Once again the formula in Column F has adjusted where the others have not, they are limited by the fact that they refer to the first fifteen values of Column B. On the other hand, the formula in Column F refers to all of the values in Column B. So that's what I mean by a Dynamic Range formula.

Write a Comment
For this to be truly dynamic then column f needs to shrink when column b shrinks. Under your method I can not get column f to shrink. When column b shrinks, error messages occur in the other columns. An "If(Error(Function))" would hide the errors but that would take up space and process time. So how do you shrink column b and adjust the other columns accordingly?
Posted by Charlie III at October 22, 2004 20:40
Hi Charlie,

Good question and here are my answers (as I see them) ;-)

I have it on good authority that array formulas cannot be shrunk, only deleted and re-entered. At least that's what I have read, and as far as I can see it is true until someone proves me wrong (people do it all the time!)

2. Neither can arrays can be really expanded unless you do it manually. Not that much better than deleting and re-entering it to shrink, but left alone the formula in Column F still adjusts to according to the number of rows it is expanded to.

That's what I am really after and it is the reason for my caution in stating what I mean by a dynamic range. (The other array formulas are limited by the ranges entered, B2:B16)

Like you point out, a true dynamic range expands or shrinks automatically. So in the case of shrinking perhaps formatting to hide errors may be an answer depending on what is required (a Custom Number format (enter [Black]General) as the Cell Format, then using white font will do the trick as will Conditional Formatting using something like ISERROR)

A named range using =OFFSET($A$1,0,0,COUNTA($A:$A),1) is a dynamic range but as far as I can see, it doesn't work with blanks cells whereas the formula in Column F does.

I could also use a much more simpler formula such as =LARGE(B:B,ROW()) entered in the first row and dragged down , but as with the array formula, if there are more cells with either formula entered than there are values, you will get an error message. When using formatting to hide errors in this case, the LARGE and ROW formula may still show Error Indicators in the top right of the cells depending on your Excel version but these can be removed on the Error Checking tab from Tools, Options.

Different methods, different results. Experimenting is always fun, that's what Excel should always be!
Posted by Andrew at October 22, 2004 23:59
Hello Andrew & Charlie III,

My approach to your problem is a bit different. I do not use array formulas and they are really expandable.

Say that in column B you have your values, like in your example.

In C2 I write 1, in C3 the formula "=1+C2".

In D2 I write the formula "=LARGE(B:B,C2)", which is not an array formula, and I copy it to D3.

Now, I select cells C3 and D3 and I drag them down till wherever.

If I happen to add more figures in column B, I can always drag the last 2 cells of columns C&D.

Hope you like it!

Vicente Soler
Barcelona, Spain
Posted by Vicente Soler at January 07, 2005 05:16
Hi Vicente,

Nice method!

I think array formulas have advantages and disadvantages. One big disadvantage is they can slow things down but sometimes they can be replaced by regular formulas or VBA. Depending on the situation, there can be many criteria that

Here's another way I thought up at work.

=SMALL(B:B,COUNT(B:B)-(ROW(B2)-2))

It aslo works with blank cells and just one column, it must be dragged also in the case that new numbers are added.

But =LARGE(B:B,ROW()) in Row 1 is still my favourite in this case ;-)
Posted by Andrew at January 07, 2005 18:53