In this post I would like to present a solution to the situation in which we wish to count the number of rows for which a stipulated condition is met in at least one of several columns.
To illustrate what is meant by this, consider the extract below:
which details levels of scrap nickel exports for various countries and for various years (you can download the workbook here).
I recently received a request from James, who was interested in a formula-based solution to the following problem: given a two-dimensional range containing a mixture of numbers and empty cells (which I am defining as being either “genuinely” empty or as containing the null string “” as a result of formulas in those cells), generate a unique list of those numbers in order of their frequency within that range, with the most frequent first. What’s more, if two or more numbers occur the same number of times within that range, then they should be listed in order of their size from smallest to largest.
For example, for the dataset in A1:F6 below, we would return the list as given beginning in I1.
We usually face no problems in cases where we wish to apply a formula to, not just one, but an array of values. And of course we do this by simply committing the formula as an array formula, i.e. with CSE.
However, not all formulas yield so easily, and some stubbornly resist any attempts at coercing an array of returns from them. Here I would like to discuss some techniques which, in addition to array-entry, can help coerce the desired result.
The principal method in such cases is to use a construction involving OFFSET, though a set-up using INDEX is equally viable; indeed, due to its non-volatility, perhaps even preferable. Some cases may require even more coercion than that, and others less. But the one thing they all share in common is that, on its own, array-entry just isn’t enough!
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:
ABCDEF123456
the now-ubiquitous solution:
=0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))
will correctly return 123456.
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
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.
We can do this with the following set-up: More…
We are often in a position where we wish to exclude certain values from an array of results before passing that array to another function.
For example, a common, practical situation is that of finding the minimum value from a range whilst excluding zeroes. This can be done in several ways, for example using an array formula:
=MIN(IF(A1:A10<>0,A1:A10))
or, if we have Excel 2010 or later, using AGGREGATE:
=AGGREGATE(15,6,A1:A10/(A1:A10<>0),1)
