We sometimes look for non-array (i.e. non-CSE) versions of constructions which would normally require array-entry. Our reasons for doing so may be varied:
1) We may feel that it improves spreadsheet performance (sometimes true, sometimes not)
2) We perhaps have a dislike for having to use the required keystroke combination necessary for committing array formulas
3) We may simply be interested from a theoretical point of view
This post would certainly belong more to the third category than to the other two. The necessary coercions to force TRANSPOSE to operate as normal without array-entry are convoluted and impractical. Though that is not to say that they are not without interest, and the use of these coercions is of course not exclusive to TRANSPOSE. In fact, they can be employed within a number of functions to generate a return which would otherwise require array-entry.
As an example, take the dataset below.
Supposing we wish to identify at which point the running total from A2 downwards equals or exceeds a certain total, let’s say 5. Those running totals are shown in column B, though let’s assume for whatever reason that we do not wish to – or cannot – use such a helper column in our solution. And supposing we wish to return the number of cells which it was necessary to sum in order to reach that total.
Clearly here the answer is 3, since 1+2+3=6 is the first occasion on which our target of 5 has been surpassed.
There are two standard techniques for obtaining this result. One is to use an array set-up with SUBTOTAL and OFFSET. The other, which I prefer and use here, is to employ an array construction using MMULT, i.e.:
Many functions are such that we can generally substitute the need for CSE by introducing an INDEX function, appropriately placed within the formula (see here for a discussion on this technique).
Other functions, including TRANSPOSE, do not yield so easily, and so in order to generate a non-array version of these functions we must look to other means.
The non-array equivalent to the above formula is:
i.e. we require the addition of not just INDEX, but also the N function.
And if the array being passed to TRANSPOSE is not some other function, but an actual range reference, then the necessary coercion for the non-array equivalent is even more involved (and certainly not to be recommended, apart from perhaps to those who have a morbid fear of array formulas and so seek to avoid them at all costs!). For example, given the following, practical example:
we may wish to take the sum of the “cross”-products of B1 with A2, C1 with A3, D1 with A4 and E1 with A5, i.e.:
(The parentheses aren’t strictly necessary, though I put them in for clarity.)
The answer is 70, as you can easily verify. Our first attempt at forming an abbreviated version of this formula might naturally be to try SUMPRODUCT:
though the result, 260, is not what we would have hoped for. Since the two arrays in question – B1:E1 and A2:A5 – are orthogonal, i.e. one is a single-column vector, the other a single-row vector, Excel quite rightly forms a total of all “cross”-products for those values, equivalent to:
where each of the entries is the product of the values from B1:E1 and A2:A5 corresponding to its position within the grid. The grand total of all these entries then gives the 260 we see as a result.
Clearly, then, in order to get the more “simple” product of B1:E1 and A2:A5, we need to first ensure that these two arrays are of the same vector-type, i.e. either both are single-row vectors or both are single-column vectors.
As some of you may know, we do this by transposing one of the arrays (either will do, though not both, since then we will be back where we started). However:
gives a result of 52, not the required 70. The reason being (and you can see this if you go through it step by step using the Evaluate Formula tool) that SUMPRODUCT alone is not capable of coercing TRANSPOSE to operate on all elements within the range A2:A5. Hence, the above resolves (unhelpfully) to:
since only the first element in the range A2:A5 is returned by TRANSPOSE, and so we obtain 52 as a result.
By entering the formula as an array formula (in which case we don’t need SUMPRODUCT; SUM will suffice), we do obtain the necessary coercion, and this time:
does resolve to 70, as desired.
But can we obtain this result without using either the long-hand multiplication method (which might quickly become impractical if we had a much larger dataset) or an array formula?
The answer is yes, tnough, as I said, the necessary coercions are far from obvious, and arguably no less “impractical”. Nevertheless, the non-array:
will return the required value of 70.
I leave it to the reader to investigate the techniques outlined here, both in their use with TRANSPOSE and also applied to other functions. Avoiding array-entry is not to be recommended merely for the sake of avoiding array-entry, though it can certainly (as here) reveal some interesting theoretical points about formula syntax, and it should be borne in mind that the techniques outlined are not without any practical use.