Advanced Formula Challenge #7: Results and Discussion 1

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

This is a trickier problem than it at first appears, and indeed there are several pitfalls which prevent us from using more “standard” techniques to arrive at a solution.

Perhaps the two main (hidden) obstacles, which were not immediately obvious from the examples I gave, are, firstly, the fact that we are prevented from using a construction involving a SEARCH-approach (e.g. by locating occurrences of each substring of the four types *????*, †????*, *????† and †????†, as John Jairo V attempted), since this of course presumes that there is only one occurrence of each of those substring types within our string, a presumption which cannot be made.

More…

Converting from base 10 to base x Reply

This is a formula-based solution which, given a positive number in base 10, converts that number into its equivalent form for another, given positive base.

Convert from base 10 to base x

So, given a base-10 value to be converted in A2, and the base to which we wish to convert this value in B2, the required output is generated in C2. Cell D2 contains a “back-check” which re-converts the value in C2 to base 10.

The formula in C2 is:

=SUMPRODUCT(MOD(FLOOR(A2/B2^(ROW(INDIRECT("1:20"))-1),1),B2)*10^(ROW(INDIRECT("1:20"))-1))

More…

Non-Array TRANSPOSE 4

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

More…

Advanced Formula Challenge #6: Results and Discussion Reply

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

This one was perhaps a little less complex than ones I’d set in previous weeks, though of course it would still, in my opinion, fall within the boundaries of what I would deem “advanced Excel”.

It also demonstrates some techniques which we can apply to solving problems involving non-contiguous ranges, and in particular tell us which functions may be applicable to such set-ups.

Two good solutions received from John Jairo V and cyrilbrd (and Bill‘s was practically there as well, but for a small amendment – and the fact that I didn’t structure the question in full to begin with – sorry!).

More…

List of unique entries from column of space-separated strings 5

Given the list below in A1:A10, we may wish to create a list of unique, single words from that list, as per column B here.

Unique From Space Separated

We can do this with the following set-up: More…

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:

More…

Advanced Formula Challenge #4: Results and Discussion Reply

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

This one turns out to be a good deal more complex than it at first appears, and so perhaps not surprisingly no correct results were received..

GreasySpot at first thought that Advanced Filter would be a viable solution, but quickly realised that it wasn’t actually appropriate here. Besides, as I mentioned, the idea of this (and of all these challenges in fact) is to try to achieve the results using worksheet formulas alone.

So how can we achieve our desired results?

More…

Advanced Formula Challenge #3: Results and Discussion 7

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:

More…

Advanced Formula Challenge #2: Results and Discussion 3

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.

More…

ROW vs ROWS for consecutive integer generation 7

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.:

=INDEX($B1:$B10,SMALL(IF($A$1:$A$10="A",ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10))+1),1))

More…

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.

More…

Advanced Formula Challenge #1: Single column from many (containing blanks) (2) – Columns first 3

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.

More…

Single column from many (containing blanks) (1) – Rows first 24

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.

More…