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” criteria.
For example, given the following data:
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: