Criteria with Statistical Functions (GROWTH, LINEST, LOGEST, TREND) 2

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.

More…

Shortest Formula Challenge #6: Results and Discussion Reply

A couple of weeks ago I set readers the challenge which can be found here.

Once again, some truly excellent responses and a noticeably collaborative attempt towards obtaining our final, minimal-length solution. So many thanks to all who contributed: Alex, John Jairo, Lori, Snakehips and Will!

And that solution, at 108 characters, is:

=LOOKUP(,0/FREQUENCY(0,2^-(LEN(Q5:Q77)>4)/MMULT(SUMIF(U3:U28,MID(Q5:Q77,COLUMN(A1:G1),1),V3),1^V3:V9)),Q5:Q6)

How does it work?

More…

Incrementing Indirect Column References Within SUMIF(S)/COUNTIF(S) 13

Most Excel users are aware that, when a formula containing relative column references is copied to further columns, those references are updated accordingly. So, for example, the formula:

=SUMIFS(C:C,$A:$A,"X",$B:$B,"X")

when dragged to the right, will become, successively:

=SUMIFS(D:D,$A:$A,"X",$B:$B,"X")
=SUMIFS(E:E,$A:$A,"X",$B:$B,"X")

etc., etc.

And so we have a relatively (no pun intended) simple means by which we can obtain a conditional sum from successive columns.

But what if the range we wish to increment is being referenced indirectly? For example, what if we are using a version of the above, but in which the sheet being referenced is dynamic, viz:

=SUMIFS(INDIRECT("'"&$A$1&"'!C:C"),INDIRECT("'"&$A$1&"'!A:A"),"X",INDIRECT("'"&$A$1&"'!B:B"),"Y")

where A1 contains the sheet name (e.g. “Sheet1”) which is to be referenced at any given time?

More…

Redimensioning 18

Readers who have read some of my earlier posts will be familiar with the concept of “redimensioning” an array.

This is an extremely useful and important technique, which, in its basic form, allows us to take a two-dimensional array and convert it into one of just a single dimension, whilst of course retaining the elements within that array.

Such an approach is necessary if we wish to further manipulate the entries of some two-dimensional array. For example, we might be in a position in which, for whatever reason, we need to pass each of the entries in a two-dimensional array to an array of one or more parameters for further processing. However, since the evaluation of the resulting multi-dimensional “matrix” is not within Excel’s capabilities, we are obliged to first transform the original array to one of a single dimension.

More…

Unique, Ordered List of Most Frequent Numbers in a Two-Dimensional Range 8

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.

Unique, Ordered List of Most Frequent Numbers in a Two-Dimensional Range

More…

Shortest Formula Challenge #3: Results and Discussion 1

Last week I set readers the challenge which can be found here.

This one provoked quite a bit of debate, and not all of it Excel-related! As I already have to several readers, I must again apologize for the lack of realism and statistical know-how inherent in the premise for this challenge, which was evidently constructed more with the required formula-work in mind than with any serious thought to methods in demography.

Still, at least some fascinating and impressive Excel work came out of it all, so perhaps my poor groundwork is somewhat forgiven, at least retrospectively!

More…

Unique, Alphabetical List from Several Columns 25

In this post I shall present a method for generating a unique, alphabetical list in a single column from data contained within a contiguous range comprising several columns.

For example, given the dataset below in A2:E5, we will return that list beginning in cell G1:

Unique, Alphabetical List from Several Columns

More…

List of unique entries from column of space-separated strings 3

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.

Unique From Space Separated

We can do this with the following set-up: More…

COUNTIFS: Multiple “OR” criteria for one or two criteria_Ranges 64

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:

More…

Advanced Formula Challenge #5: Results and Discussion 5

Last week I set readers the challenge which can be found here.

This is a reasonably complex problem, and certainly so if we want to present a solution which is relatively concise. However, despite its complexity (and arguably lack of practical use), the solution demonstrates some important techniques for working with strings, and so is not without merit.

The required set-up is as follows:

More…