Advanced Formula Challenge #1: Results and Discussion 4

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.

To re-cap, the task was to come up with a formula in H1 which, when copied down an arbitrary number of rows, generates the non-blank entries from Range1 (in this case A1:D6) as below.

Several Columns from Many (2) 150

The entry in cell F1 was given to the reader, and is derived from the formula:

=SUM(COUNTIF(Range1,{">0","?*"}))

i.e. counts the number of non-blank cells in Range1.

The way I approached this problem was to concentrate on adapting the solution for generating the column G entries to also work for the column H entries. And this is the result (array-entered):

=IF(ROWS($1:1)>$F$1,"",INDIRECT(TEXT(SUM(10^{5,0}*MID(TEXT(SMALL(IF(Range1<>"",10^5*COLUMN(Range1)+ROW(Range1)),ROWS($1:1)),"R00000C00000"),{8,2},5)),"R00000C00000"),0))

How does it work?

Firstly, let’s remind ourselves of the formula solution in G1 for generating the same results but in a “rows-first” fashion (dissected in the previous post):

=IF(ROWS($1:1)>$F$1,"",INDIRECT(TEXT(SMALL(IF(Range1<>"",10^5*ROW(Range1)+COLUMN(Range1)),ROWS($1:1)),"R0C00000"),0))

We can see that the two solutions are largely similar: indeed, the main clause of that solution, i.e.:

TEXT(SMALL(IF(Range1<>"",10^5*ROW(Range1)+COLUMN(Range1)),ROWS($1:1)),"R0C00000")

is practically identical to that in this solution, i.e.:

TEXT(SMALL(IF(Range1<>"",10^5*COLUMN(Range1)+ROW(Range1)),ROWS($1:1)),"R00000C00000")

the only differences being a small change in the format_text parameter of the TEXT function and the fact that the COLUMN and ROW functions have “switched roles”.

The previous post featured a breakdown of the logic inherent in this clause, and readers are advised to go through it if they have not already done so. For the purpose of the discussion here, let’s look at what this part will resolve to for the formula in one cell chosen at random, say H5, that is:

TEXT(SMALL(IF(Range1<>"",10^5*COLUMN(Range1)+ROW(Range1)),ROWS($1:5)),"R00000C00000")

which is “R00002C00005”, as you may wish to verify.

Now, if you look back at the diagram, you’ll see that the result in H5 – 65 – lies in the 5th row and 2nd column of Range1. All we need to do then is to somehow swap the 2 for the 5 in this construction and we will have it in the desired format to pass to INDIRECT.

In fact, the realisation that we can utilize the same approach for this columns-wise solution as was employed for the rows-wise solution is the hard part: the remaining required manipulation is relatively straightforward, viz:

MID(TEXT(SMALL(IF(Range1<>"",10^5*COLUMN(Range1)+ROW(Range1)),ROWS($1:5)),"R00000C00000"),{8,2},5)

becoming:

MID("R00002C00005",{8,2},5)

which returns the array {“00005″,”00002”}, and you may be familiar with the technique we are using here to obtain our desired string “reversal”, since we now pass this to the larger clause to obtain:

INDIRECT(TEXT(SUM(10^{5,0}*{"00005","00002"}),"R00000C00000"),0)

which is:

INDIRECT(TEXT(SUM({500000,2}),"R00000C00000"),0)

and then:

INDIRECT(TEXT(500002,"R00000C00000"),0)

and we should be happy by now since we know that this will resolve, as required, to 65.

I leave it to the reader to work out why “R00000C00000” was preferred in the main clause in this version to the previous “R0C00000”.

Another formula challenge to follow shortly. Watch this space!

4 comments

  1. @XOR LX,
    Assuming Range1 will not have errors
    How is

    =SUM(COUNTIF(Range1,{">0","?*"}))

    different from

    =COUNTA(Range1)
  2. @sam

    Hi and welcome to the site!

    Only because years of experience have taught me to account for null strings as well as for “genuine” blanks (we often have formulas in our cells which are set to return those null strings, i.e. “”, under certain conditions).

    If there were any such strings in the range, then COUNTA would unhelpfully include them in its count, whereas the construction I used will not.

    Thanks for the contribution!

    Regards

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