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…

VLOOKUP Across Several Worksheets (2) – Multiple Search Criteria 9

I recently made the post here, in which I presented a solution to the problem of returning a value based upon matching a single criterion in a given column across multiple worksheets.

In this follow-up post I will look at the analogous case in which we are not matching a single criterion, but several. As mentioned in the first instalment, I will look at two solutions to this problem, one in which we make use of an extra “helper” column in each of the relevant worksheets, and one in which we do without such aids.

More…

Shortest Formula Challenge #3: World Populations 47

The challenge this week is as follows: based on the below data extract of populations for a selection of countries for the four years from 2010-2013 inclusive, a single formula in A1 to identify the Region which had the largest average yearly percentage increase in population over that period.

World Populations

The answer here would be Sub-Saharan Africa. For readers wishing to corroborate their results, the average yearly percentage increase in population for that region was 2.9840070521% to 10 d.p.

More…

VLOOKUP Across Several Worksheets (1) – One Search Criterion 4

Most people with an average level of ability in Excel are perfectly capable of using VLOOKUP when this operation is performed over a range within a single worksheet.

But what happens when we wish to extend our search to multiple worksheets, and so return the first match from whichever sheet happens to be the first which contains our search value(s)?

In this post I will present a solution for such cases in which we have a single criterion to be matched in a given column across multiple worksheets.

In the next instalment (to follow shortly) I will also look at cases in which we are not matching a single criterion, but several. In this situation by far the simplest method is to use an extra “helper” column in each of the relevant worksheets in which we first perform a concatenation of the fields of interest. By doing this we ensure that it is a relatively straightforward case of extending the solution designed for one criterion to work also with multiple criteria.

More…

Shortest Formula Challenge #2: Results and Discussion 5

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

5 correct solutions received, courtesy of John Jairo V, GreasySpot, Bill Szysz, James and ChrisBM (who actually missed off a final parenthesis in his formula, though I will be lenient here!). So well done to all!

As to whose was the shortest, excluding the offering from Isai Alvarado, who beat everyone with his 51-character (excluding the equals sign) Google Sheets construction (well done Isai!), that accolade is shared by John and Bill, both of whose solutions came in at 56 characters, which is quite a remarkable coincidence when you consider that each used a completely different construction! So congratulations to John and Bill!

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 #9: Results and Discussion Reply

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

One correct solution received, courtesy of Lori, who not only presented a fine construction for working in Excel 2010 and earlier, but also a 2013 version, which had the added benefit of taking advantage of some of the new (and evidently very useful) features of that version to noticeably abridge the required set-up. So many thanks to Lori for sharing this knowledge and also congratulations on an excellent solution to a particularly complex challenge!

More…

Coercing array returns from CSE-resistant formulas 13

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!

More…

Extracting numbers from a string 4: All numbers to a single cell 2

This is the fourth in a series of discussions on the techniques available for extracting numbers from an alphanumeric string.

In the first instalment in this series (which can be found here) I looked at extracting consecutive numbers which appear at the start of the string, e.g. 123ABC456.

In the second instalment (here) I looked at extracting consecutive numbers which appear at the end of the string, e.g. 123ABC456.

In the third instalment (here) I looked at extracting all numbers from a string where each of those numbers was to be returned to a separate cell. For example, given the string 81;8.75>@5279@4.=45>A?A; we extracted 81, 8.75, 5279, 4 and 45 into individual cells.

In this post I will look at a technique for extracting all numbers from a string, but where those numbers are to be returned as a single number in a single cell.

More…

Advanced Formula Challenge #9: One Per Row, One Per Column 8

The challenge this week is as follows: given a 4×4 grid, here in A1:D4, in which each entry is a single-digit integer, and also a target value, here in F2, which is also an integer, though ranging from 1-36, a single formula in G2 to return the number of combinations of four distinct values from that grid whose total is equal to the value in F2, subject to the condition that each of those four values cannot occupy the same row or column as any of the other three.

In the example below, the answer would be 6:

One Per Row, One Per Column

since the following are the only 6 arrangements which meet the conditions outlined above:

One Per Row, One Per Column (2)

Solution next week. Best of luck!