Category Archives: Esoteric

I wouldn’t normally publish a post on such an esoteric topic as this. However, since the idea for it came as a result of a challenge posed by the venerable David Hager, I felt that I could not resist.

And that challenge was as follows: given a list of chemical elements and their respective atomic weights, a formula to determine the weight for a given molecule.

It goes without saying that there are numerous quick and easy online applications which will perform such a calculation. Nevertheless, and however unlikely it may seem, there is still a small probability that this post will reach one or more of the tiny minority who have a practical need for such calculations to be performed within Excel (and, in addition, perhaps without recourse to VBA).

Note to readers: this post has been updated due to the inclusion – at the request of Torstein – of a further version of this solution, in which the number of values to be considered is dynamic and so may be set by the user. This version may be found at the very end of this post.

This post, inspired by a question from Patrick MacKay, from Belgium – thanks, Patrick! 🙂 – is a (rather belated) follow-up to that which I made here, in which, to recap, I presented a formula-based set-up which, given a target figure plus a series of values, determined which, if any, combination of those values had a sum equal to the target.

The only slight drawback to that solution was the caveat that, if more than one combination of values existed which satisfied that condition, then only one of those combinations was given.

Here I would like to improve upon that set-up by presenting a refined version which will return all such combinations. What’s more, at the very end of this deconstruction I will give a further version of the solution in which the number of values to be considered is a variable which may be set by the user.

In fact, that early post was also one of the very few in which I did not give an explanation as to how the solution works, which I would like to do here.

As an example of the output, imagine that our target value – £1054.35, for example – is here in A1, and that we have a list of 10 values in A2:A11, as below:

Inspired by a recent query at one of the Excel forums I occasionally visit, I would like to share a formula-based solution for the task of generating an nxn grid of random integers, where each of those integers is unique within that range.

For example, for the case of n=10, we might have, in A1:J10:

where I have formatted the cells in this range as custom type: 00 (applying a TEXT function to the formula would complicate matters, in the sense that this would interfere with the functioning of our FREQUENCY construction).

At the time of writing (Saturday morning, UK time; apologies if anyone has submitted something after that date), two correct solutions received (or three if you count non-Excel-based ones: as he has done for most of the recent challenges, Isai Alvarado produced a solution applicable to Google Sheets, which, as usual, I am unable to verify! So I’m taking your word for it that it’s perfectly correct, Isai! 🙂 ).

The two correct entries came courtesy of Snakehips, who gave a rather lengthy but perfectly correct solution, and John Jairo V, who improved upon his earlier attempt by producing a solution which, in essence, used a similar approach to Snakehips’ but which made use of some very nice technique involving MMULT to considerably abbreviate the required construction. Great work, John!

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.

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.

A slightly light-hearted post this, as you may have guessed from the title, though readers might find it moderately interesting, and hopefully some may even contribute to my rather esoteric collection of pandigital numbers in Excel.

This began as a result of seeing (I don’t recall where now, unfortunately) an alternative version of the tried-and-tested construction for returning a number from the end of a mixed string. For example, given the following in A1:

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.

Sometimes we are in a situation where we have a target figure plus a series of values and we want to know which, if any, combination of those values has a sum which is equal to the target.

This can be done as follows:

Edit: this post has now been revised here to account for multiple returns, should that be a requirement.

Using the above set-up, with our target value in A2 and our (in this case 9) values in C1:K1, we will place formulas in C2:K2 which will contain an “X” if the value in the row above forms part of our solution.

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.

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

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.