Advanced Formula Challenge #13: Results and Discussion 6

Apologies to all for the hiatus – hopefully I will have a bit more time now in which to post some new material.

Anyway, before beginning anything new, I would just like to offer my thanks to Alex, Lori and Michael for their illuminating contributions to the challenge I set a while back and which can be found here.

Another challenge to follow shortly. Watch this space!

Advanced Formula Challenge #13: Single Array Containing All Entries From a Given Range in Multiple Worksheets 22

The challenge this week is as follows: a single formula to return an array which consists of all entries (of potentially mixed datatype) from a given range in multiple worksheets.

No restrictions are placed on the dimensions of the returned array.

For example, using the attached file (download here), the formula should return an array such as:

`{18,"",19,63,"","",67;"",46,"","","L","",7;"N","Z","","F",70,19,"";"","","","","","",73;"","","T","","",88,"Y";"U","","B","F","Q",71,"";"","H","U","R",86,84,"Q";11,"R",84,"","",43,72;39,53,"","","R",60,80;"","V","B",33,"",46,39;"","B","","","V","","";"P","","M","","","",85;"O","",9,"","","","";"","I",9,"","S","S",59;"","X","J","","","","X";46,72,"",22,"",67,"P";35,32,69,"O","","","V";24,"","","","R","","";"",5,"B",69,"C","","";"P","A",19,"","","K","B"}`

or a dimensional variation thereof.

For the sake of consistency, the specified range (A1:G4 in the attached example) should be referred to as the Defined Name Rng in any formulas. Likewise for any references which refer to a collection of worksheets, e.g.:

`={"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}`

which should be given the Defined Name Sheets.

Also please note that this is not a Shortest Formula Challenge! Hence rigour should be preferred over brevity in any solution.

Solution next week. Good luck!

Update: Formula Wrapping Issue in Firefox and Google Chrome 1

I have now put in place a solution which means that the entire contents of a given formula should be visible in all cases, i.e. those contents do not extend off the edge of the screen. This should have fixed the issue not only in Google Chrome, but hopefully also in Firefox.

The resulting formatting is not quite as I would have wished, though it at least means that posts are no longer rendered unreadable.

I would just like to add a belated thanks to Vaibhav, whose suggestion many months ago has proved to be extremely useful. So thank you, Vaibhav, and sincere apologies for not having resolved this issue for my Firefox readers until now.

Regards

Apologies to Readers: Formula Wrapping Issue in Firefox and Google Chrome 2

It has just come to my attention that, due to a recent update in Google Chrome, this browser no longer supports the particular CSS word-wrap property which is a feature of all formulas posted on this site.

As such, any formulas viewed in that browser, or in Firefox, will not be wrapped and, as such, those beyond a certain length will not be viewable.

I understand that this issue has always been present for readers who use Firefox (thanks to Bert for bringing this to my attention). I also appreciate that this means that the vast majority of my posts will lose their appeal due to their unintelligibility, for which I can only apologise.

I am currently working on a solution to this issue and will hopefully have a fix soon.

Regards

Molecular Weights 11

I wouldn’t normally publish a post on such an esoteric topic as this. However, since the idea for it came as a result of a challenge posed by the venerable David Hager, I felt that I could not resist.

And that challenge was as follows: given a list of chemical elements and their respective atomic weights, a formula to determine the weight for a given molecule.

It goes without saying that there are numerous quick and easy online applications which will perform such a calculation. Nevertheless, and however unlikely it may seem, there is still a small probability that this post will reach one or more of the tiny minority who have a practical need for such calculations to be performed within Excel (and, in addition, perhaps without recourse to VBA).

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

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.

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?

Shortest Formula Challenge #6: Scrabbled 33

The challenge this week is as follows (you can download the workbook here):

It is you to start in a game of Scrabble, and your rack of letters (cell Q2) is DGTAROZ.

You are a strong enough player to have deduced a list of all possible words from the Engligh language of two or more letters which can be formed from this rack. Given that list in Q5:Q77, derive a single formula to return the word from that list which returns the highest score when placed on the board (A5:O15).

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?

Advanced Formula Challenge #12: Results and Discussion 5

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

Such was the number and variety of responses to this challenge that presenting a detailed breakdown of one such solution – as has been the case for all of the first eleven in this series of challenges – would, I feel, be somewhat inappropriate.

For the majority of these challenges, it could be argued that there has been one solution which is indisputably “better” than the rest. Perhaps such an adjudication can also be made here, though to do so would certainly not be a straightforward exercise. What’s more, to pick just one of the many solutions would be to leave the rest – unfairly in my opinion – left on the sidelines.

As such, I would refer the readers to the many solutions in that post and to enjoy dissecting the varied and wonderful constructions therein. And to simply thank all those – Alex, aMareis, Maxim, John Jairo, sam, Jeff, Lori, Ron, Michael, Christian and XLarium – whose excellent contributions led to such a fruitful and inspiring discussion.

There’s evidently still much to be discovered in the world of worksheet formulas!

Another challenge to follow shortly. Watch this space!

Advanced Formula Challenge #12: An Array of Matches 81

The challenge this week is as follows: given a range of arbitrary size in which each entry is either 0 or 1 and in which each row contains at least one occurrence of a 1, a single formula to return an array consisting of the relative column positions of the first occurrence of a 1 within each row.

For example, given the below in A1:E10:

the solution would be the array:

`{2;1;1;2;1;5;1;4;1;3}`

Readers may reference the range A1:E10 in their solution, though of course being aware that this choice is purely arbitrary and hence that any solution must also hold for a range of any size.

Readers should also note that the entries in the returned array are to be the relative column positions within the range (just as if we’d used MATCH on each of the rows within that range). As such, moving the above range to, for example, H1:L10 would have no impact on the output of any solution.

Also note that this is NOT a shortest formula challenge!

Solution next week. Good luck!

Redimensioning 24

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.

Simultaneous Locating of First and Last Numbers in a String 23

I was initially debating whether to give this post a more pragmatic title, such as “Extracting Phone Numbers from a String”, that being one of the more common practical applications for the techniques outlined here.

However, the extraction of phone numbers (I’m referring here to that type which employs some form of delimiter, e.g. 1-800-12345, and not that which comprises a non-delimited numerical string, e.g. 180012345, there existing already well-documented formula techniques for the extraction of the latter – although of course the set-up given here will work for those as well) is certainly not the only use for this method, and so, in the end, I chose to go with a less restrictive, more theoretical title.

Shortest Formula Challenge #5: Results and Discussion 2

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

Surprisingly, no-one managed to come up with the shortest solution. Dozens of attempts came very close, yet all were either ineligible or syntactically incorrect.

As such, the shortest correct solution was also the very first received, courtesy of pjc, viz:

`2*ROW(A1:A2)-3`

So many congratulations to pjc, even the more so as this was their very first post at this site! Fantastic start, pjc!

Shortest Formula Challenge #5: No Array Constants, Please 18

I was recently helping someone to develop a formula-based Conditional Formatting rule, a stipulation for which (at least in 2010) is that the explicit use of array constants is forbidden.

Of course, we can get around this by first storing these array constants as Defined Names and then referencing those in the formula instead, which is perfectly legitimate.

On that particular day, however, I was in a rather creative mood (no doubt to the OP’s detriment!), and so went about the (somewhat convoluted) task of creating a version of my formula in which any array constants were replaced with suitable functional constructions, only to find that this process was not always quite as straightforward as I had first presumed…

Extracting Numbers of Set Length Only from Alphanumeric Strings 20

In this post I would like to present a solution to the practical problem of extracting a number of defined length from an alphanumeric string which may contain several numbers of varied lengths.

Indeed, the inspiration behind this post is in part derived from having personally witnessed many such requests on the various Excel forums, most of which involve the extraction of e.g. an account number of fixed length, 6 digits, say, from a longish string containing many other numbers.

As an example, given the following string:

`20/04/15 - VAT Reg: 1234567: Please send 123456 against Order #98765, Customer Code A123XY, £125.00`

we may wish to extract the one occurrence of a 6-digit number (123456) from that string.

Shortest Formula Challenge #4: Results and Discussion 5

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

A good response to this one, leading to a solution for which, in the end, most people who responded can take some credit.

Snakehips started the ball rolling with a nice logical construction involving “OR”ing two separate COUNTIFs; John Jairo V then shaved off several characters from this solution; this was then further refined by Elias; and, finally, after several attempts at constructing a solution using FREQUENCY, Alex Groberman took the COUNTIF set-up and wrapped it in that most wonderful of functions – MODE.MULT – to give us our winner.

Shortest Formula Challenge #4: Consecutive Integers 33

The challenge this week is as follows: given the range A1:D5, in which each of the entries is an integer and is unique within that range, a single formula to generate an array consisting of all values which form part of a consecutive sequence of at least two entries from that range. The elements within this array are also to be returned in an order from smallest to largest.

For the example below:

one possible answer would be (as highlighted):

`{1;2;3;12;13;14;15;16;17;36;37}`

Counting Rows Where Condition Is Met In At Least One Column 63

In this post I would like to present a solution to the situation in which we wish to count the number of rows for which a stipulated condition is met in at least one of several columns.

To illustrate what is meant by this, consider the extract below:

which details levels of scrap nickel exports for various countries and for various years (you can download the workbook here).

Return Entry Corresponding to Maximum Value Based on Conditions 55

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:

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”.