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…

Return Entry Corresponding to Maximum Value Based on Conditions 39

We are often faced with the practical situation in which we need to return the entry from a certain column which corresponds to the maximum numerical value from another column subject to one or more conditions.

For example, from the table below:

INDEX_MAX_IF Non-array Alternative

we may wish to return the date (column C) which corresponds to the latest version (column B) for a given order number (column A), where by “latest” we mean “largest numerically”.

More…

Grid of Random Integers 3

Inspired by a recent query at one of the Excel forums I occasionally visit, I would like to share a formula-based solution for the task of generating an nxn grid of random integers, where each of those integers is unique within that range.

For example, for the case of n=10, we might have, in A1:J10:

Grid of Random Integers

where I have formatted the cells in this range as custom type: 00 (applying a TEXT function to the formula would complicate matters, in the sense that this would interfere with the functioning of our FREQUENCY construction).

More…

Sorting a List Alphabetically (Without Filters) 17

In this post I would like to present a formula-based solution which returns an alphabetically-sorted list of the entries from a given range. Effectively, then, the formula gives equivalent results to those obtained using the in-built sort feature (though which, for whatever reasons, we may not be in a position to use).

For example, given the unsorted list in A2:A11 as below, we will return the ordered results as given in B2:B11.

Sorting a List Alphabetically (Without Filters)

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…

Advanced Formula Challenge #6: Results and Discussion Reply

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

This one was perhaps a little less complex than ones I’d set in previous weeks, though of course it would still, in my opinion, fall within the boundaries of what I would deem “advanced Excel”.

It also demonstrates some techniques which we can apply to solving problems involving non-contiguous ranges, and in particular tell us which functions may be applicable to such set-ups.

Two good solutions received from John Jairo V and cyrilbrd (and Bill‘s was practically there as well, but for a small amendment – and the fact that I didn’t structure the question in full to begin with – sorry!).

More…

Advanced Formula Challenge #4: Results and Discussion Reply

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

This one turns out to be a good deal more complex than it at first appears, and so perhaps not surprisingly no correct results were received..

GreasySpot at first thought that Advanced Filter would be a viable solution, but quickly realised that it wasn’t actually appropriate here. Besides, as I mentioned, the idea of this (and of all these challenges in fact) is to try to achieve the results using worksheet formulas alone.

So how can we achieve our desired results?

More…