In this post I would like to expatiate on a technique which has been hinted at previously (and so which may already be familiar to regular readers), though which I’d like to make explicit, and, what’s more, within the context of a post which outlines one of the most practical uses for that technique.
As most readers will no doubt know, the vast majority of Excel functions are able to ignore Booleans (and sometimes, where appropriate, other non-numerics) within the range passed. As such, they effectively operate over a reduced range which comprises the non-Booleans (or numerics) only, allowing us to include conditional statements (generally using IF) within our function so as to restrict which values are – ultimately – processed by our construction.
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 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…
In this post I would like to clear up what appears to me to be a rather widespread misunderstanding of how COUNTIFS/SUMIFS operate, in particular when we pass arrays consisting of more than one element as the Criteria to one or even two of the Criteria_Ranges.
This latter technique is used when the criteria in question are to be considered as “OR” criteria, which is not to be confused with cases where we wish the criteria passed to be calculated rather as “AND” critieria.
For example, given the following data:
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:
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.
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.