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:
the now-ubiquitous solution:
will correctly return 123456.
For those who don’t already know, the concatenation of A1 with the string “0123456789” is necessary so as to prevent MIN from returning an error. Since, if A1 did not contain all of the digits from 0-9, then at least one of the returns from the FIND function would be an error. And since MIN is not capable of ignoring error values the whole construction would therefore also result in an error.
By first catenating “0123456789” to the end of the string in A1, we both ensure that A1 now contains at least one of each of the digits from 0-9 (so that all 10 returns from FIND will be non-error values), whilst ensuring, by virtue of having placed this string at the end of A1, that we don’t affect the required output for the MIN function.
Anyway, when I first stumbled upon this variation:
I was at first bemused. Then, when it dawned on me what was happening here, I myself started to prefer the use of 7^18 in this construction in place of the automatic “0123456789”.
It wasn’t long before I switched – thanks to shg at over at EF – to using 5^19 instead (a smaller pandigital number!). And, once I’d grown tired of that one, I thought that I may as well see how many other mathematical operations I could find which resulted in a “pandigital” number.
Of course, there are an infinite number of such operations, but the caveats were that the total number of characters within the operation be as short as possible and, of course, that it be reproduceable in Excel.
Here’s my current list:
My “check” formula in C2 is:
The last two are certainly pushing the criterion of being as short as possible, though never having used WEIBULL in all the time I’ve been working in Excel, I thought that I could at least remedy that by using it here. Not that WEIBULL was designed with such constructions in mind, but hey! (I have yet to find a way to produce a pandigital number using BAHTTEXT, by the way. 🙂 )
Anyone wishing to add to my list, please do!