Complex array formula

Colin Legg · #1 02-01-2012, 06:52 AM

Hi,

Okay, this is a pretty weird one...

First, to explain the error.
The array formula in L2 which is returning an #VALUE! error is this:

Code:

=MIN(IF(
    INDIRECT(ADDRESS(ROW(),COLUMN(INDIRECT(F2))+1)&":"&CELL("address",$E2))>0
    ,{1,2,3},{0,0,0}))

Now, let's look at the (outer) INDIRECT component of the formula:

Code:

INDIRECT(ADDRESS(ROW(),COLUMN(INDIRECT(F2))+1)&":"&CELL("address",$E2))

INDIRECT is a function which returns a reference to a range and, in this case, the range reference being returned is C2:E2.

Sometimes when INDIRECT is used in a formula, when it returns the range reference, the range can be implicitly dereferenced, meaning that the formula can read the values from that range.

On other occassions, the range reference cannot be implicitly dereferenced and you have to give the formula a helping hand in getting the values from the cells - otherwise you will get an error. This is driven by the context of the formula - what other functions INDIRECT is nested inside of, so it would be very difficult to come up with a hard a fast rule as to when you do/don't have to do this.

In this case the rest of the formula can't work out what it needs to do with C2:E2 when it's returned. We are dealing with numbers, so we can give the formula a hand by using the N() function to convert the C2:E2 reference into an array of numbers.

Code:

=MIN(IF(
    N(INDIRECT(ADDRESS(ROW(),COLUMN(INDIRECT(F2))+1)&":"&CELL("address",$E2)))>0
    ,{1,2,3},{0,0,0}))

The N() function is basically saying, "Give me the numbers from C2:E2". Other numerical functions would work too, for example SUM(), but I think the intention of their use is less clear:

Code:

=MIN(IF(
    SUM(INDIRECT(ADDRESS(ROW(),COLUMN(INDIRECT(F2))+1)&":"&CELL("address",$E2)))>0
    ,{1,2,3},{0,0,0}))

If you would like, I can show you how to prove all this by using some VBA to perform an analysis.

As an aside, I would mention that using array formulas to put cell addresses into cells and then using those addresses in subsequent formulas is very unusual. Typically you would try to get the range reference and use it directly.

Quote:

By the way, can you recommend some good books / articles / posts etc. about advanced arrays?

My take on array formulas is that learning them is important because if you understand them well then you'll understand pretty much any formula that's thrown at you. That said, it's very often the case that when it comes to building your spreadsheets, you should avoid them. They can have quite a penalty on performance speed and they are complicated so it is often better to split them out into smaller and simpler formulas.
As a result, there really isn't much source material on them. A few years ago I wrote an introductory tutorial on them here:
http://www.xtremevbtalk.com/showthread.php?t=296012
and there are some links in the last post which may be of interest.

There are several Excel worksheet functions which can return the Range structure you saw in this post, including OFFSET() and INDEX(). There have been a few questions on them on the online forums, such as this one:
http://www.excelforum.com/excel-work...k-in-name.html

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Complex Find and Replace	paulkaye	Word	1	11-13-2011 04:23 AM
Modifying subject in Hyperlink - Complex	amritr	Outlook	0	05-26-2011 11:20 PM
Searching for Word Reformatter for Complex Docs	jchapman	Word	1	05-05-2011 04:50 PM
Look up an array based on user input	johnsmb	Excel	2	01-07-2011 01:12 PM
Complex Document Formatting	rguima	Word	0	02-22-2009 04:31 PM