Tag Archives: Advanced Formula Challenges

Advanced Formula Challenge #8: Facetious? Moi? 25

Advanced Formula Challenges • Tags:

The challenge this week is as follows: given a single paragraph of text in A1, which may or may not contain punctuation, a single formula in B1 to identify the number of words within that text which contain all five vowels of the English alphabet precisely once each and in an order of appearance, from left to right, of a, e, i, o, u.

For the below example the result would be 8, as highlighted in red.

Advanced Formula Challenge 8 Facetious Moi v2

Edit: I have now amended the text to make it clearer that certain words are not to be considered in the count: those in black, for example, do not meet the requirements as outlined above.

The workbook can be downloaded here.

Solution next week. Best of luck!

Advanced Formula Challenge #6: Results and Discussion Reply

Advanced Formula Challenges • Tags: , , , , , , , , , , , ,

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…

Advanced Formula Challenge #6: Occurs once, non-contiguous range 9

Advanced Formula Challenges • Tags:

The challenge this week is as follows: given a non-contiguous range, made up of an arbitrary number of single-column ranges, the values in each of which being either a numeric, text or null string, to generate a list, in numerical order and beginning in A2, of all numbers which occur precisely once within that range.

For example, in the below:

Occur Once Non-Contiguous Ranges

More…

Advanced Formula Challenge #5: Results and Discussion 5

Advanced Formula Challenges, String Extraction • Tags: , , , , , , , , , , , , , , , , ,

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 #5: Threes, Fives and Sevens Reply

Advanced Formula Challenges • Tags:

The challenge this week is as follows: given an alphanumeric string of arbitrary length in A1, derive a single formula to return the number of numbers within that string which are divisible by either 3, 5 or 7.

By “divisible” here I mean of course that there is no remainder after division.

And by “numbers within that string” I mean all consecutive substrings of any length within that string which may be interpreted as a number. (It can also safely be assumed that there are no alphanumeric combinations within the string in A1 which would be interpreted by Excel as numeric, e.g. JAN01.)

For example, the string:

XX30X5XXX42XX771

contains, by this definition, 13 numbers: 3, 0, 30, 5, 4, 2, 42, 7, 7, 1, 77, 71 and 771.

More…

Advanced Formula Challenge #4: Results and Discussion Reply

Advanced Formula Challenges, Esoteric • Tags: , , , , , , , , , , , , , ,

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

Advanced Formula Challenges, Esoteric, Manipulating Arrays • Tags: , , , , , , , ,

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 #3: Numbers From Hyphen-Separated List 19

Advanced Formula Challenges, Extracting Data • Tags: ,

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:


Picture1

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.

More…

Advanced Formula Challenge #2: Results and Discussion 3

Advanced Formula Challenges, Esoteric • Tags: , , , , , , , , , , , ,

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…

Advanced Formula Challenge #2: Identifying Anagrams 9

Advanced Formula Challenges, Esoteric • Tags: ,

For this, the 2nd in the series of Advanced Formula Challenges, readers are asked to come up with a solution to the following:

Identifying Anagrams

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.

More…

Advanced Formula Challenge #1: Results and Discussion 4

Advanced Formula Challenges, Extracting Data • Tags: , , , , , , , ,

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

Advanced Formula Challenges, Extracting Data • Tags: , , , , , ,

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…