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”.
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.
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:
(You can download the workbook here.)
Solution next week. Best of luck!
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:
EXCELXOR 