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.

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…

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…

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

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

One solution was received, again from Bill, and this time it was not only correct, but a very good solution indeed. So congratulations again to Bill!

In fact, rather than dissect my own solution this week (which in any case differs only in minor details from Bill’s), I would like to present a breakdown of the solution given by Bill, as follows:

More…