The challenge this week is as follows: given the two lists of numbers in A2:A12 and B2:B12, generate the list as per the Results column, beginning in C2:
A single formula should be placed in C2 such that, when copied down an arbitrary number of rows, generates the results as above.
Last week I posed readers the challenge which can be found here.
One solution was received, again from Bill, and this time it was not only correct, but a very good solution indeed. So congratulations again to Bill!
In fact, rather than dissect my own solution this week (which in any case differs only in minor details from Bill’s), I would like to present a breakdown of the solution given by Bill, as follows:
Perhaps one of the most widely-believed myths surrounding INDEX is that it is not possible to coerce a return of more than one value from the array passed to it.
At first sight, and after various attempts at coercing such a return (including array-entry), it does indeed seem for all the world that INDEX cannot be so induced, and stubbornly persists in returning just the first element in any array generated.
Granted, the required coercions are not obvious (the first of which is borderline tautological: the necessary initial clause, IF(1,… – or any other suitable value in place of 1 – is self-evidently TRUE), though, for example, the following non-array formula:
Given the list in A1:A6, where some entries consist of a single number and some of a grouping of numbers (where e.g. 13-16 represents 13, 14, 15 and 16), the challenge this week is to come up with a single formula in D1 which, when copied down an arbitrary number of rows, produces a list of all individual, ungrouped numbers from the list in A1:A6, as here:
The formula is to return a blank in rows beyond the expected number of returns. What’s more, this must be done via reference to a second formula, in C1, also to be derived and whose value is to equal the total expected number of non-blank entries to be returned in column D for any given dataset.
The property of INDEX of being able to return entire rows/columns has several important applications, one of which is to force an array of returns to be passed to another function which otherwise would require entering as an array formula, i.e. with CSE.
For example, the following formula, one possibility for returning the relative position of the first non-blank cell in the range A1:A10:
Last week I set readers the challenge which can be found here.
Three solutions were offered, two of which from the same person, and both of which were correct! So many congratulations to Bill on successfully solving what was quite a complex challenge!
Indeed, as Ben Schwartz pointed out, this challenge appears to have been set previously on the internet, and seems to have been only partially solved on those occasions. In any case, thanks also to Ben for his suggestion, which he confesses was cobbled together from those previous solutions he found, and which worked in all but a few exceptional cases.
Often we wish to incorporate into our formula a construction which, as that formula is copied down to successive rows, will generate a series of consecutive integers, usually beginning with 1.
A classic example is the standard INDEX/SMALL set-up for returning multiple values corresponding to a certain set of criteria, e.g.:
For this, the 2nd in the series of Advanced Formula Challenges, readers are asked to come up with a solution to the following:
Given two lists of names in B1:B10 and E1:E10 (as above), a formula is to be entered into A1, such that, when copied down to A10, returns TRUE if, for the corresponding name in column B, there exists at least one name in the range E1:E10 which is an anagram of that name.
Last Sunday I set a challenge to readers to come up with a solution to the problem here.
Even though this site’s only been up for one week, I’m quite happy to have received the single solution that I did, even more so since that solution was a correct one, from John Jairo Vergara Domìnguez, whose offering you can see if you scroll down to the bottom of that link. Thanks again, John, and well done!
As excellent as John’s solution was, it would still require a little tweaking to work for other ranges (part of its construction is dependent on the array in question being in certain columns within the worksheet) and, in any case, I would now like to present the solution that I developed for this problem.
If either (or sometimes both – see below) of the row or column parameters is zero, or omitted, and the INDEX is properly coerced (e.g. forms part of a larger formula), a reference to the entire specified column or row is generated. Note that the return here is not a single value, but rather an array of values.
We are not so much interested in returning this array in an actual cell somewhere (which in any case we can’t do), but more in being able to reference/manipulate that array in further calculations. For example:
We saw in a previous post (here) an outline for a solution which, given a two-dimensional array, potentially containing some empty cells, generated a list of all non-blank entries from that array in a single column.
In that solution the returned entries were listed in an order which is consistent with the entries from an entire row from the original array being returned prior to moving onto those in the next row. The converse, in which entries are returned in a columns-first fashion, is the challenge I would like to set for any readers of this post willing to have a go.
Given a two-dimensional array, potentially containing some empty cells, it is sometimes desirable to create a list of all non-blank entries from that array in a single column.
In general, it is not a major concern in which order the returns appear in this new column, and indeed the “standard” solution for this problem is the one given here, in which those returns are listed in an order which is consistent with the entries from an entire row from the original array being returned prior to moving onto those in the next row. The converse, in which entries are returned in a columns-first fashion, will be the subject of my first Advanced Formula Challenge post to follow this one.