Dynamic Name Range

The MATCH returns the row number of the last number to be found in Column C.

The INDEX takes the MATCH result to create a range reference

Assume last number appears in row 10 of column C, the named range thus becomes:

Please Login or Register  to view this content.

obviously your range above spans three columns which may or may not be required

If you have a multi column defined name and wish to work only with a specific column you can use a further INDEX in your standard functions, eg:

Please Login or Register  to view this content.

which would return the MAX of the last column in the range (as specified by 3; the 0 for row tells INDEX to return all rows)

That's what's known as a BigNum.

This might help explain it:

http://www.mrexcel.com/forum/showthread.php?t=18954

Dom

Thanks Domski! I appreciate the read. I will have to read it a few times to make sure that I comprehend all the info in that article.

I'm guessing that there is no better way to do the Maxif

@olsoniv, when working with huge data sets etc the #1 key to efficient calculations (IMO) is ensuring the data set is sorted appropriately.

Consider a data set of:

A1:A40000: Alphas
B1:B40000: Alphas
C1:C40000: Alphas
C1:C40000: Numbers

Let's further assume we wish to:

Return MAX of C where A = "x", B = "y" and C = "z"
(to be repeated for a multitude of other combinations which may/may not exist)

First things first it makes sense to create a "key" of the items of interest to reduce no. of comparative tests we need to perform:

Please Login or Register  to view this content.

the use of delimiters is important as you can adapt below to incorporate wildcard-esque searches


Q: Is Data Set - Sorted ?

A: No:

we're left with an Array approach (assume Pivots [intermediate/final] not viable)

Please Login or Register  to view this content.

As we know Arrays are "expensive" in terms of resource so this approach is going to be slow.

A: Yes - by A (A to Z), B (A to Z) and C (A to Z)

with the data set sorted as outlined we can use more efficient approaches

Please Login or Register  to view this content.

the above repeated over and over will prove far quicker than the unsorted/Array approach simply because we're only calculating the MAX of a specific range (identified by INDEX & MATCH calls)

[obviously in a real file the constant references to ^x^y^z^ would be replaced by a function concatenating values of interest]


If you can't sort your data I would advise creating an intermediate Pivot table to calculate the MAX etc for the various combinations and then a basic GETPIVOTDATA call to retrieve the appropriate values.


If you need assistance specific to your file you will need to post a sample

Data cannot be sorted. I love the key idea! when performing wildcard searches, i'm assuming I use the "match" function and use the "*" wildcards between delimiters?

Please Login or Register  to view this content.

would evaluate to true... This worked in my tests but i just wanted to check to make sure I wasn't missing a more efficient way to do this.

Originally Posted by olsoniv

i'm assuming I use the "match" function and use the "*" wildcards between delimiters?

How best to utilise wildcard approach would largely depend on context but in the case of this type of Array, yes, a MATCH approach would be logical.

Using:

Please Login or Register  to view this content.

tells XL that the first item is irrelevant

Please Login or Register  to view this content.

tells XL that the 21st century is significant

other functions like COUNTIF etc accept wildcards and so you can perform multi criteria tests using this method - useful pre XL2007 (and introduction of the IFS)

OK...so I'm still struggling with this thing and feel that i must be doing something stupid. I've attached an example book so you can see exactly what I'm trying to do.

Dynamically count the arrays using BigNum as suggested while leaving out the "" results from a formula (in this case I just did ="" for the example)
and then find the max min and average using the key and year to filter the results.

Please Login or Register  to view this content.

I also tried with match and got no result as well.

I appreciate your continued guidance on this issue. I am very frustrated that I haven't been able to work this out on my own and need to keep bugging you on the matter.

Thanks in advance.

Your DNR ArrayKey is defined incorrectly - your MATCH of BigNum in Col A will return an error given no numbers in A.

In reality it would make sense to size each range based on a consistent column, so define all based on match of BigNum in Col B for ex.

Please Login or Register  to view this content.

With the above corrected you can then work on your MAX Arrays

When you use wildcards in a MATCH you must use an exact match_type as final parameter.
This argument is by default optional and if omitted TRUE/1 is assumed (not an exact match - errs towards binary search for efficiency reasons)

Please Login or Register  to view this content.

note the ,0 after ArrayKey in the MATCH
(0 could equally be FALSE given 0 & FALSE are in this context the same thing)

The 2nd MAX approach won't work as a x=y won't allow for wildcards.

If you only ever need to look for a single year at any given time you could add year to the key itself.