Advanced Formula Challenge #11: Results and Discussion 11

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

Good results for this one: six answers received, six correct answers received – from Oscar, Daniel, diondan1, Bill, Ikkeman and Calvin. Plus one (unverifiable, though no doubt correct!) Google Sheets solution from Isai, as usual. ๐Ÿ™‚

So congratulations to all of the above!

The majority of those solutions adopted a strategy of comparing the characters from two sets of arrays derived using MID over an array of start_num parameters, though a couple of solvers (Bill and Calvin) decided to first derive the ASCII codes for these characters and instead use these as the basis for the comparison.

More…

Advanced Formula Challenge #11: All in Order 28

The challenge this week is as follows: given a value in A2, where A2 is a string consisting of upper-case letters of the alphabet only and of minimum character-length 2, a formula to be entered into B2 such that, if and only if the individual characters within the string in A2 are in ascending alphabetical order (from left to right), return TRUE; otherwise return FALSE.

Examples below:

All in Order

(You can download the workbook here.)

Solution next week. Best of luck!

Which numbers add up to total? (2): Multiple Solutions 14

Note to readers: this post has been updated due to the inclusion – at the request of Torstein – of a further version of this solution, in which the number of values to be considered is dynamic and so may be set by the user. This version may be found at the very end of this post.

This post, inspired by a question from Patrick MacKay, from Belgium – thanks, Patrick! ๐Ÿ™‚ – is a (rather belated) follow-up to that which I made here, in which, to recap, I presented a formula-based set-up which, given a target figure plus a series of values, determined which, if any, combination of those values had a sum equal to the target.

The only slight drawback to that solution was the caveat that, if more than one combination of values existed which satisfied that condition, then only one of those combinations was given.

Here I would like to improve upon that set-up by presenting a refined version which will return all such combinations. What’s more, at the very end of this deconstruction I will give a further version of the solution in which the number of values to be considered is a variable which may be set by the user.

In fact, that early post was also one of the very few in which I did not give an explanation as to how the solution works, which I would like to do here.

As an example of the output, imagine that our target value – ยฃ1054.35, for example – is here in A1, and that we have a list of 10 values in A2:A11, as below:

Which Numbers Add Up To Total (Multiple Solutions)

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…

Advanced Formula Challenge #10: Prime Factorisation 23

The challenge this week is as follows: given a value in A2, where A2 is an integer and 2<=A2<=100, a formula to be entered into B2 such that, if the value in A2 is a prime number, return "Prime"; otherwise, return a string representing the prime factorisation of that number, using the full notation as given in the below examples (where a lower-case "x" is used to denote multiplication).

Prime Factorisation

For those not sure of any results, I have given a full list in the attached here.

Solution next week. Best of luck!

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

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…

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!

Advanced Formula Challenge #8: Results and Discussion 2

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

At the time of writing (Saturday morning, UK time; apologies if anyone has submitted something after that date), two correct solutions received (or three if you count non-Excel-based ones: as he has done for most of the recent challenges, Isai Alvarado produced a solution applicable to Google Sheets, which, as usual, I am unable to verify! So I’m taking your word for it that it’s perfectly correct, Isai! ๐Ÿ™‚ ).

The two correct entries came courtesy of Snakehips, who gave a rather lengthy but perfectly correct solution, and John Jairo V, who improved upon his earlier attempt by producing a solution which, in essence, used a similar approach to Snakehips’ but which made use of some very nice technique involving MMULT to considerably abbreviate the required construction. Great work, John!

More…

Extracting numbers from a string 3: All numbers to individual cells 29

This is the third 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 this post I will demonstrate a technique for extracting all numbers from a string where:

  • The string in question consists of a mixture of numbers, letters and special characters
  • The numbers may appear anywhere within that string
  • Decimals within the string are to be returned as such
  • The desired result is to have all numbers returned to separate cells

More…