Advanced Formula Challenge #5: Results and Discussion 5

Last week I set readers the challenge which can be found here.

This is a reasonably complex problem, and certainly so if we want to present a solution which is relatively concise. However, despite its complexity (and arguably lack of practical use), the solution demonstrates some important techniques for working with strings, and so is not without merit.

The required set-up is as follows:

First go to Name Manager and, making sure that the active cell in the worksheet is somewhere in row 1, define:

Arry1 as:

=ROW(INDIRECT("1:"&LEN($A1)))

Arry2 as:

=ROW(INDIRECT("1:"&LEN($A1)^2))-1

Arry3 as:

=1+INT((Arry2)/LEN($A1))

and finally Arry4 as:

=1+MOD((Arry2),LEN($A1))

The required array formula is then:

=SUM(0+(MMULT(IFERROR(0+(MOD(INDEX(MID(A1,Arry1,IF(1+LEN(A1)-Arry1>=TRANSPOSE(Arry1),TRANSPOSE(Arry1))),N(IF(1,Arry3)),N(IF(1,Arry4))),{3,5,7})=0),0),{1;1;1})>0))

How does it work?

I’ll be demonstrating this solution by using the example string given in last week’s challenge, i.e.:

XX30X5XXX42XX771

The principal part here is played by the MID function, which is:

MID(A1,Arry1,IF(1+LEN(A1)-Arry1>=TRANSPOSE(Arry1),TRANSPOSE(Arry1)))

I’ll look at each of MID’s two parameters in turn. The first, the start_num parameter, is given by Arry1, which recall is:

ROW(INDIRECT("1:"&LEN($A1)))

and so simply generates an array of integers from 1 up to the length of the string in A1, which is 16 in this example, i.e.:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}

The second parameter for MID, num_chars, is given by:

IF(1+LEN(A1)-Arry1>=TRANSPOSE(Arry1),TRANSPOSE(Arry1))

Now, just before deconstructing this part, I should mention that the usual method here would be to do without the IF clause in the above and use instead the simple TRANSPOSE(Arry1) for this parameter, which would like look:

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}

If we were to now resolve:

MID(A1,Arry1,TRANSPOSE(Arry1))

this would be:

MID("XX30X5XXX42XX771",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16})

Followers of my posts here will hopefully be familiar with the way in which such constructions are resolved, in which the two arrays being passed as parameters are orthogonal, i.e. one a single-column array (MID’s start_num parameter here), the other a single-row array (the num_chars parameter).

As such, these two arrays, each consisting of 16 entries, will generate a 16-by-16 matrix, the entries in which will comprise the results of passing all 256 possible pairs of parameters in turn to MID.

For example, the first element in this matrix of results will be that equivalent to performing MID(A1,1,1), i.e. “X”, the second equivalent to performing MID(A1,1,2), i.e. “XX”, and so on all the way up to MID(A1,16,16), i.e. “1”. In fact, in full it will look like this:

{"X","XX","XX3","XX30","XX30X","XX30X5","XX30X5X","XX30X5XX","XX30X5XXX","XX30X5XXX4","XX30X5XXX42","XX30X5XXX42X","XX30X5XXX42XX","XX30X5XXX42XX7","XX30X5XXX42XX77","XX30X5XXX42XX771";"X","X3","X30","X30X","X30X5","X30X5X","X30X5XX","X30X5XXX","X30X5XXX4","X30X5XXX42","X30X5XXX42X","X30X5XXX42XX","X30X5XXX42XX7","X30X5XXX42XX77","X30X5XXX42XX771","X30X5XXX42XX771";"3","30","30X","30X5","30X5X","30X5XX","30X5XXX","30X5XXX4","30X5XXX42","30X5XXX42X","30X5XXX42XX","30X5XXX42XX7","30X5XXX42XX77","30X5XXX42XX771","30X5XXX42XX771","30X5XXX42XX771";"0","0X","0X5","0X5X","0X5XX","0X5XXX","0X5XXX4","0X5XXX42","0X5XXX42X","0X5XXX42XX","0X5XXX42XX7","0X5XXX42XX77","0X5XXX42XX771","0X5XXX42XX771","0X5XXX42XX771","0X5XXX42XX771";"X","X5","X5X","X5XX","X5XXX","X5XXX4","X5XXX42","X5XXX42X","X5XXX42XX","X5XXX42XX7","X5XXX42XX77","X5XXX42XX771","X5XXX42XX771","X5XXX42XX771","X5XXX42XX771","X5XXX42XX771";"5","5X","5XX","5XXX","5XXX4","5XXX42","5XXX42X","5XXX42XX","5XXX42XX7","5XXX42XX77","5XXX42XX771","5XXX42XX771","5XXX42XX771","5XXX42XX771","5XXX42XX771","5XXX42XX771";"X","XX","XXX","XXX4","XXX42","XXX42X","XXX42XX","XXX42XX7","XXX42XX77","XXX42XX771","XXX42XX771","XXX42XX771","XXX42XX771","XXX42XX771","XXX42XX771","XXX42XX771";"X","XX","XX4","XX42","XX42X","XX42XX","XX42XX7","XX42XX77","XX42XX771","XX42XX771","XX42XX771","XX42XX771","XX42XX771","XX42XX771","XX42XX771","XX42XX771";"X","X4","X42","X42X","X42XX","X42XX7","X42XX77","X42XX771","X42XX771","X42XX771","X42XX771","X42XX771","X42XX771","X42XX771","X42XX771","X42XX771";"4","42","42X","42XX","42XX7","42XX77","42XX771","42XX771","42XX771","42XX771","42XX771","42XX771","42XX771","42XX771","42XX771","42XX771";"2","2X","2XX","2XX7","2XX77","2XX771","2XX771","2XX771","2XX771","2XX771","2XX771","2XX771","2XX771","2XX771","2XX771","2XX771";"X","XX","XX7","XX77","XX771","XX771","XX771","XX771","XX771","XX771","XX771","XX771","XX771","XX771","XX771","XX771";"X","X7","X77","X771","X771","X771","X771","X771","X771","X771","X771","X771","X771","X771","X771","X771";"7","77","771","771","771","771","771","771","771","771","771","771","771","771","771","771";"7","71","71","71","71","71","71","71","71","71","71","71","71","71","71","71";"1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1"}

which is an array consisting of all substrings – beginning in all positions and of all lengths – of our string in A1.

However, when we come to counting the number of strings in this array which meet our condition (whether they are divisible by 3, 5 or 7), we may face problems with duplicated strings, in which case our count would be larger than the true value.

If you look at just the last few values in this array, it is clear that 771, 71 and 1, for example, appear far more times than we would like. The reason being that, for example:

=MID(A1,14,3)

which you can easily verify is “771”, is identical to all of:

=MID(A1,14,4)
=MID(A1,14,5)
=MID(A1,14,5)

=MID(A1,14,16)

by virtue of the fact that MID does not error when the num_chars parameter effectively takes it beyond the end of the string in question, but instead treats that parameter as if it had in fact taken it precisely to the end of the string.

As such, we need to develop an IF clause which reduces this array to one in which we have no repeated strings. This will mean, for each of the 16 rows of results in the 16-by-16 matrix we have generated, passing TRUE to all those values which are either not duplicated within that row or are the first occurrence of that value within the row, and FALSE to those which are otherwise.

The required array of Booleans can be generated using:

1+LEN(A1)-Arry1>=TRANSPOSE(Arry1)

since this resolves to:

1+16-{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}>=TRANSPOSE({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16})

which is:

{16;15;14;13;12;11;10;9;8;7;6;5;4;3;2;1}>={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}

i.e. we perform 256 tests as to whether each of the 16 values in the first array is greater than or equal to each of the 16 values in the second, the result of which will look like:

{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

Even though we are not actually doing so here, perhaps if I temporarily coerce these Booleans to their numerical equivalents and condense the width appropriately, then it will become clearer what this array represents:

{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0;1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0;1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0;1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0;1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0;1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0;1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0;1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0;1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0;1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0;1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

What this means, then, is that, for the first row in our 16-by-16 matrix of results, which corresponds to taking MID(A1,1,1), MID(A1,1,2), MID(A1,1,3), …, MID(A1,1,16), we will allow all of these, since clearly none of these will be duplicates of each other.

And that, for the second row of 16 substrings, corresponding to taking MID(A1,2,1), MID(A1,2,2), MID(A1,2,3), …, MID(A1,2,16), we will allow all but the very last of these, since MID(A1,2,15) and MID(A1,2,16) are both equal to “X30X5XXX42XX771”.

And so on and so on. The end result being that we will have achieved our desired array containing no duplication. Hence the IF clause you can see, which means that:

IF(1+LEN(A1)-Arry1>=TRANSPOSE(Arry1),TRANSPOSE(Arry1))

becomes, after resolving our array of Boolean TRUE/FALSE conditions:

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16;1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,FALSE;1,2,3,4,5,6,7,8,9,10,11,12,13,14,FALSE,FALSE;1,2,3,4,5,6,7,8,9,10,11,12,13,FALSE,FALSE,FALSE;1,2,3,4,5,6,7,8,9,10,11,12,FALSE,FALSE,FALSE,FALSE;1,2,3,4,5,6,7,8,9,10,11,FALSE,FALSE,FALSE,FALSE,FALSE;1,2,3,4,5,6,7,8,9,10,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;1,2,3,4,5,6,7,8,9,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;1,2,3,4,5,6,7,8,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;1,2,3,4,5,6,7,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;1,2,3,4,5,6,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;1,2,3,4,5,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;1,2,3,4,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;1,2,3,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;1,2,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;1,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

and so this means that:

MID(A1,Arry1,IF(1+LEN(A1)-Arry1>=TRANSPOSE(Arry1),TRANSPOSE(Arry1)))

resolves to:

{"X","XX","XX3","XX30","XX30X","XX30X5","XX30X5X","XX30X5XX","XX30X5XXX","XX30X5XXX4","XX30X5XXX42","XX30X5XXX42X","XX30X5XXX42XX","XX30X5XXX42XX7","XX30X5XXX42XX77","XX30X5XXX42XX771";"X","X3","X30","X30X","X30X5","X30X5X","X30X5XX","X30X5XXX","X30X5XXX4","X30X5XXX42","X30X5XXX42X","X30X5XXX42XX","X30X5XXX42XX7","X30X5XXX42XX77","X30X5XXX42XX771","";"3","30","30X","30X5","30X5X","30X5XX","30X5XXX","30X5XXX4","30X5XXX42","30X5XXX42X","30X5XXX42XX","30X5XXX42XX7","30X5XXX42XX77","30X5XXX42XX771","","";"0","0X","0X5","0X5X","0X5XX","0X5XXX","0X5XXX4","0X5XXX42","0X5XXX42X","0X5XXX42XX","0X5XXX42XX7","0X5XXX42XX77","0X5XXX42XX771","","","";"X","X5","X5X","X5XX","X5XXX","X5XXX4","X5XXX42","X5XXX42X","X5XXX42XX","X5XXX42XX7","X5XXX42XX77","X5XXX42XX771","","","","";"5","5X","5XX","5XXX","5XXX4","5XXX42","5XXX42X","5XXX42XX","5XXX42XX7","5XXX42XX77","5XXX42XX771","","","","","";"X","XX","XXX","XXX4","XXX42","XXX42X","XXX42XX","XXX42XX7","XXX42XX77","XXX42XX771","","","","","","";"X","XX","XX4","XX42","XX42X","XX42XX","XX42XX7","XX42XX77","XX42XX771","","","","","","","";"X","X4","X42","X42X","X42XX","X42XX7","X42XX77","X42XX771","","","","","","","","";"4","42","42X","42XX","42XX7","42XX77","42XX771","","","","","","","","","";"2","2X","2XX","2XX7","2XX77","2XX771","","","","","","","","","","";"X","XX","XX7","XX77","XX771","","","","","","","","","","","";"X","X7","X77","X771","","","","","","","","","","","","";"7","77","771","","","","","","","","","","","","","";"7","71","","","","","","","","","","","","","","";"1","","","","","","","","","","","","","","",""}

(MID returns an empty string when the num_chars parameter is zero, or, equivalently, FALSE.)

Now, having generated an array of all unique substrings – beginning in all positions and of all lengths – from our string in A1, what we now need to do is to test each of these values as to whether it is divisible by 3, 5 or 7.

Of course, we could do this by first seeing which of these values are divisible by 3, then performing a second test as to which are divisible by 5, and finally a third test to determine which are divisible by 7.

But apart from the fact that this would make any solution extremely unmanageable in length, we also have to consider the fact that, had I instead set the challenge to test for divisibility against, not just three numbers, but, say fifty, then this method of multiple-testing would become even more unfeasible.

No. What we ideally want is the ability to simultaneously test for divisibility by an array of values, not just one. Just as we saw here with the MID function, in which we passed not just a single value to each of its parameters, but whole arrays, we would like to carry out a similar operation with regards to testing for divisibility.

What this means it that, for the entries in the 16-by-16 matrix we have just generated, we would like to pass an array of 3 values (3, 5 and 7) as the divisor parameter to a single MOD function operating over all 256 of those entries.

Now, if those 256 entries were not in a 16-row-by-16-column array, but instead in a single column (or row), then this would be straightforward. Indeed, readers may wish to verify that, given a single-column array consisting of 256 entries, e.g. in A1:A256, a construction such as:

MOD(A1:A256,{3,5,7})

properly array-coerced (by some other function, for example SUM), would quite correctly produce a 256-row-by-3-column array, the entries of which would consist of the results of applying the MOD function with divisor parameters of each of 3, 5 and 7 in turn to the 256 entries in A1:A256.

Unfortunately we have a bit of an issue…

And that is because we have, to put it bluntly, “run out of dimensions”.

The reason we are able to perform such an operation with our first array being a single-column array (as in the example using A1:A256) is that, just as when we processed our MID function over two sets of orthogonal arrays, here again we are operating with two arrays which are orthogonal but also, crucially, both one-dimensional.

And when we perform some operation in Excel in which we are passing two one-dimensional, orthogonal arrays to some function, the result is, not surprisingly, two-dimensional.

But what happens when one or more of the two arrays in question has more than one dimension? Just as in our case, what is the dimensionality of the array resulting from passing a two-dimensional array (our 16-by-16 matrix of results from the MID) and a one-dimensional array (our three parameters that we wish to pass to MOD)?

You may have guessed (or can visualize) that the answer is of course: three-dimensional.

And this is why I say that we have a a bit of an issue, since, unfortunately, that’s one dimension too many for (the majority of) Excel functions. Although we can quite easily visualize such a three-dimensional set of results (imagine a 16x16x3 cuboid in Cartesian x-y-z space, each point in which corresponding to the result of applying MOD with a parameter of, in turn, 3, 5 and 7 to each of 256 values, themselves the result of applying MID to our string in A1 with all permutations of pairs from a set of values from 1 to 16…), Excel is just not at that “level of consciousness” yet.

And so we are left with two choices. We either accept that we simply cannot perform this simultaneous array-processing over all three values (3, 5 and 7) in our MOD function, and so revert to the less-than-ideal method I outlined previously, in which we perform each of these tests separately and then somehow collate the results. Or, we first make it so that this array-processing is possible.

And that’s where the (aptly-named) technique of “re-dimensioning” arrays comes in handy.

We are quite simply going to take our 16-row-by-16-column two-dimensional array of values and transform it into a 256-row-by-1-column, one-dimensional array. And we are going to do it in such a way that, of course, our values are left unchanged.

As such, as in the example above using A1:A256, we know perfectly well that we will then be able to go ahead and perform our desired array-processing with MOD.

So how exactly do we perform such a “re-dimensioning”?

We’re effectively going to INDEX each and every value within our array. In fact, we will be passing an array of 256 values to each of INDEX’s parameters. What’s more, both of these arrays will be of the same displacement (i.e. both single-column or both single-row arrays), thus ensuring that the resulting “re-dimensioned” array will be one-dimensional.

The arrays we’re going to be passing to INDEX are given by:

N(IF(1,Arry3))

for the row_num parameter, and:

N(IF(1,Arry4))

for the column_num parameter.

Let’s look at the first of these, which means we need to look at our Defined Name Arry3, which recall is:

1+INT((Arry2)/LEN($A1))

This in turn references Arry2, so let’s first deconstruct that, which is:

ROW(INDIRECT("1:"&LEN($A1)^2))-1

Our string has a length of 16, so this resolves to:

ROW(INDIRECT("1:"&16^2))-1

which is:

ROW(INDIRECT("1:"&256))-1

i.e. an array of values from 0 to 255, viz:

{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100;101;102;103;104;105;106;107;108;109;110;111;112;113;114;115;116;117;118;119;120;121;122;123;124;125;126;127;128;129;130;131;132;133;134;135;136;137;138;139;140;141;142;143;144;145;146;147;148;149;150;151;152;153;154;155;156;157;158;159;160;161;162;163;164;165;166;167;168;169;170;171;172;173;174;175;176;177;178;179;180;181;182;183;184;185;186;187;188;189;190;191;192;193;194;195;196;197;198;199;200;201;202;203;204;205;206;207;208;209;210;211;212;213;214;215;216;217;218;219;220;221;222;223;224;225;226;227;228;229;230;231;232;233;234;235;236;237;238;239;240;241;242;243;244;245;246;247;248;249;250;251;252;253;254;255}

Hence, Arry3, which is:

1+INT((Arry2)/LEN($A1))

now becomes:

1+INT(({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100;101;102;103;104;105;106;107;108;109;110;111;112;113;114;115;116;117;118;119;120;121;122;123;124;125;126;127;128;129;130;131;132;133;134;135;136;137;138;139;140;141;142;143;144;145;146;147;148;149;150;151;152;153;154;155;156;157;158;159;160;161;162;163;164;165;166;167;168;169;170;171;172;173;174;175;176;177;178;179;180;181;182;183;184;185;186;187;188;189;190;191;192;193;194;195;196;197;198;199;200;201;202;203;204;205;206;207;208;209;210;211;212;213;214;215;216;217;218;219;220;221;222;223;224;225;226;227;228;229;230;231;232;233;234;235;236;237;238;239;240;241;242;243;244;245;246;247;248;249;250;251;252;253;254;255})/16)

which is, if you follow it through a few more steps:

{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;2;2;2;2;2;2;2;2;2;2;2;2;2;2;2;2;3;3;3;3;3;3;3;3;3;3;3;3;3;3;3;3;4;4;4;4;4;4;4;4;4;4;4;4;4;4;4;4;5;5;5;5;5;5;5;5;5;5;5;5;5;5;5;5;6;6;6;6;6;6;6;6;6;6;6;6;6;6;6;6;7;7;7;7;7;7;7;7;7;7;7;7;7;7;7;7;8;8;8;8;8;8;8;8;8;8;8;8;8;8;8;8;9;9;9;9;9;9;9;9;9;9;9;9;9;9;9;9;10;10;10;10;10;10;10;10;10;10;10;10;10;10;10;10;11;11;11;11;11;11;11;11;11;11;11;11;11;11;11;11;12;12;12;12;12;12;12;12;12;12;12;12;12;12;12;12;13;13;13;13;13;13;13;13;13;13;13;13;13;13;13;13;14;14;14;14;14;14;14;14;14;14;14;14;14;14;14;14;15;15;15;15;15;15;15;15;15;15;15;15;15;15;15;15;16;16;16;16;16;16;16;16;16;16;16;16;16;16;16;16}

i.e. an array consisting of 16 ones, 16 twos, 16 threes, etc., etc., all the way up to 16 sixteens.

Readers may like to verify that Arry4, which is a very similar construction to Arry3, i.e.:

1+MOD((Arry2),LEN($A1))

resolves finally to:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}

containing precisely the same entries as Arry3, then, though arranged in a different order.

And so, if we pass these two arrays as our row and column parameters to INDEX with our large 16-by-16 matrix as the array, we will effectively return an array of results, equivalent to performing 256 separate INDEX functions:

INDEX(Our_Array,1,1)
INDEX(Our_Array,1,2)
INDEX(Our_Array,1,3)

INDEX(Our_Array,16,16)

i.e. we will be INDEXing each and every element from our 16-by-16 array precisely once. Since both of the arrays for the row_num and column_num parameters are single-column arrays, we know that the array resulting from this INDEX will also be a single-column array, meaning we have achieved our result of transforming our range of two dimensions into one of just one.

The N(IF,1 part is a necessary coercion to force INDEX to return an array of values. Readers can find details here if they are interested.

Hence, if we now look at our MMULT construction, which is:

MMULT(IFERROR(0+(MOD(INDEX(MID(A1,Arry1,IF(1+LEN(A1)-Arry1>=TRANSPOSE(Arry1),TRANSPOSE(Arry1))),N(IF(1,Arry3)),N(IF(1,Arry4))),{3,5,7})=0),0),{1;1;1})

and see that it now becomes:

MMULT(IFERROR(0+(MOD({"X";"XX";"XX3";"XX30";"XX30X";"XX30X5";"XX30X5X";"XX30X5XX";"XX30X5XXX";"XX30X5XXX4";"XX30X5XXX42";"XX30X5XXX42X";"XX30X5XXX42XX";"XX30X5XXX42XX7";"XX30X5XXX42XX77";"XX30X5XXX42XX771";"X";"X3";"X30";"X30X";"X30X5";"X30X5X";"X30X5XX";"X30X5XXX";"X30X5XXX4";"X30X5XXX42";"X30X5XXX42X";"X30X5XXX42XX";"X30X5XXX42XX7";"X30X5XXX42XX77";"X30X5XXX42XX771";"";"3";"30";"30X";"30X5";"30X5X";"30X5XX";"30X5XXX";"30X5XXX4";"30X5XXX42";"30X5XXX42X";"30X5XXX42XX";"30X5XXX42XX7";"30X5XXX42XX77";"30X5XXX42XX771";"";"";"0";"0X";"0X5";"0X5X";"0X5XX";"0X5XXX";"0X5XXX4";"0X5XXX42";"0X5XXX42X";"0X5XXX42XX";"0X5XXX42XX7";"0X5XXX42XX77";"0X5XXX42XX771";"";"";"";"X";"X5";"X5X";"X5XX";"X5XXX";"X5XXX4";"X5XXX42";"X5XXX42X";"X5XXX42XX";"X5XXX42XX7";"X5XXX42XX77";"X5XXX42XX771";"";"";"";"";"5";"5X";"5XX";"5XXX";"5XXX4";"5XXX42";"5XXX42X";"5XXX42XX";"5XXX42XX7";"5XXX42XX77";"5XXX42XX771";"";"";"";"";"";"X";"XX";"XXX";"XXX4";"XXX42";"XXX42X";"XXX42XX";"XXX42XX7";"XXX42XX77";"XXX42XX771";"";"";"";"";"";"";"X";"XX";"XX4";"XX42";"XX42X";"XX42XX";"XX42XX7";"XX42XX77";"XX42XX771";"";"";"";"";"";"";"";"X";"X4";"X42";"X42X";"X42XX";"X42XX7";"X42XX77";"X42XX771";"";"";"";"";"";"";"";"";"4";"42";"42X";"42XX";"42XX7";"42XX77";"42XX771";"";"";"";"";"";"";"";"";"";"2";"2X";"2XX";"2XX7";"2XX77";"2XX771";"";"";"";"";"";"";"";"";"";"";"X";"XX";"XX7";"XX77";"XX771";"";"";"";"";"";"";"";"";"";"";"";"X";"X7";"X77";"X771";"";"";"";"";"";"";"";"";"";"";"";"";"7";"77";"771";"";"";"";"";"";"";"";"";"";"";"";"";"";"7";"71";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"1";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""},{3,5,7})=0),0),{1;1;1})

And we can now happily process our MOD over all three values of 3, 5 and 7, which will give us a 256-row-by-3-column array, viz:

MMULT(IFERROR(0+({#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;0,3,3;0,0,2;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;0,0,0;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;2,0,5;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;1,4,4;0,2,0;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;2,2,2;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;1,2,0;2,2,0;0,1,1;0,1,1;0,1,1;0,1,1;0,1,1;0,1,1;0,1,1;0,1,1;0,1,1;0,1,1;0,1,1;0,1,1;0,1,1;0,1,1;1,2,0;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;2,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1;1,1,1}=0),0),{1;1;1})

Since our solution is to consist of cases which are perfectly divisible by either 3, 5 or 7, we are clearly interested in the zero values here. Hence the above becomes, after adding zero to the resulting Booleans and also resolving the IFERROR:

MMULT({0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;1,0,0;1,1,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;1,1,1;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,1,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;1,0,1;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1;0,0,1;1,0,0;1,0,0;1,0,0;1,0,0;1,0,0;1,0,0;1,0,0;1,0,0;1,0,0;1,0,0;1,0,0;1,0,0;1,0,0;1,0,0;0,0,1;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0},{1;1;1})

Resolving this MMULT gives:

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;3;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

And the reason for taking this MMULT becomes clear, since we now know that the elements in the above array correspond to how many of 3, 5 and 7 are factors of each of the 256 substrings we generated from the string in A1.

So the 3 in the above array, which corresponds to “0” from our string, is due to the fact that 0 is divisible by 3, 5 and 7. Or, taking another example, the first two non-zero elements of 1 and 2 in this array correspond to the strings 3 and 30, 3 being divisible by just 3, 30 by both 3 and 5.

Since we are interested in strings which are divisible by either 3, 5 or 7, any non-zero value in this array will satisfy that criterion.

Hence, finally, we test the elements in this array against that condition (>0), and sum the resulting coerced Booleans, which readers can easily verify results in a final value of 9, as required.

5 comments

  1. Not sure why but I can’t get Arry1 to evaluate. It always returns the formula and not the value. Therefore

    IF(1+LEN(A1)-Arry1>=TRANSPOSE(Arry1)

    returns a value error.

  2. What exactly do you have stored for Arry1?

    Sometimes I’ve seen people end up with quotation marks and an extra equals sign if they’ve copied and pasted into a Defined Name incorrectly, so they have something beginning: =”=… which is obviously not right.

  3. arry1=[=”ROW(INDIRECT(“”1:””&LEN($A1)))”]

    Everything inside the [brackets] but not including the [brackets].

  4. Precisely. You shouldn’t have those quotation marks as this is a formula. And they’ve been doubled-up inside the INDIRECT function as well, which is not correct. It should be:

    =ROW(INDIRECT("1:"&LEN($A1)))
  5. Ah, apologies. My fault. Just realised I didn’t have the equals signs in those Defined Names when I posted. Sorry about that – have corrected in the post now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s