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!
Hi excelxor,
Here is my answer:
=AND(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1)<=MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1))Great website by the way!
@Oscar
Nice! Can’t fault that for a solution. First answer received, and a correct one.
Presume that’s an array formula, by the way?
And thanks – glad you like it!
Cheers
Here is a solution for Google Sheets:
Thanks!
Yes, it’s an array formula.
Thanks, Isai! Keep them coming for the Sheets fans out there!
Cheers
=AND(MID(A6,ROW(INDIRECT("1:"&LEN(A6)-1))+1,1)>=MID(A6,ROW(INDIRECT("1:"&LEN(A6)-1)),1))CSE formula
=AND(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1))+1,1)>=MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1))Array formula
@Daniel
Great! Slight variation on the solution of Oscar, and equally good!
Cheers
A slightly different solution.
=AND(MMULT(CODE(MID(A2,ROW(INDIRECT("$1:"&LEN(A2)-1))+{0;1},1))*{-1;1},{1,1})>=0)Ordinary, not CSE.
Regards
Thanks, Bill!
Almost word-for-word identical to mine!
But have you double-checked this? Why are you using MMULT with {1,1} AND multiplication with {-1;1}? Is this intentional? And are you sure you’ve got the displacement of your addition vector – {0;1} – here correct?
Regards
Hi XOR LX,
You are right… this part is not necessary… I put it for better reading the first array of MMULT function only… so…we can remove this part
*{-1;1}
and change this part
{1,1} to this {-1,1}
We will get
=AND(MMULT(CODE(MID(A2,ROW(INDIRECT("$1:"&LEN(A2)-1))+{0;1},1)),{-1,1})>=0)And yes {0;1} is intentionally.
Of course we can use RIGHT function also.
=AND(MMULT(CODE(RIGHT(A2,ROW(INDIRECT("2:"&LEN(A2)))-{0;1})),{-1,1})>=0)Regards
getting rid of the volatile indirect function
CSE
@Bill
Not sure I understand.
Unless your system settings are somehow different, a semi-colon in Excel represents a row separator. Hence, when you perform the addition of:
ROW(INDIRECT("$1:"&LEN(A2)-1))and:
{0;1}both of which are single-column arrays, you are adding two arrays of the same orthogonality.
Hence, the above addition is equivalent to:
{1;2;3;4;5;6;7}+{0;1}which is:
{1;3;#N/A;#N/A;#N/A;#N/A;#N/A}I’ve asked you once if you were sure that the second array here shouldn’t actually be orthogonal to the first, i.e.:
{0,1}so I won’t ask you again!
Also, I’m not sure your other array constant (in the MMULT) is of the correct dimensionality either!
This:
{-1,1}should be this:
{-1;1}I believe.
Perhaps you can clarify?
Regards
Hi XOR,
This is my original formula
=ORAZ(MACIERZ.ILOCZYN(KOD(FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("$1:"&DŁ(A2)-1))+{0\1};1));{-1;1})>=0)As you see it is in polish. So i translate it to english (but american english) and i had to change row/column separator in constant array from ; to , (for rows) and from \ to ; (for columns). I did not know what is a row/column separator in your version of excel (regional settings).
Sorry for inconvenience.
Regards
Ah, I see. But, as I understand it, it’s only the semi-colons as argument separators which you need to change to commas, not the semi-colons used in array constants.
So you should have left the semi-colon in your original formula:
=ORAZ(MACIERZ.ILOCZYN(KOD(FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("$1:"&DŁ(A2)-1))+{0\1};1));{-1;1})>=0)as it is.
It seems as if the “\” symbol represents a column separator in array constants for your system version, correct?
Thanks for clarifying!
Cheers
Almost similar to @Oscars answer. Just added the 2 criteria check and offset function.
Learnt something new today! Thanks for the challenge @XORLX
Thanks, Calvin.
I hope that my comment editor hasn’t somehow amended your formula, but can you just re-check your results for the examples I posted?
I don’t get correct results for e.g. “OOPRRQQQ” or “LLLMNL”.
Regards
Oops! I guess my brain wasn’t working quite well!
This is the corrected version:
Thanks @XORLX
Much better! Nice!
Of course, I did state in the preamble that: “…where A2 is a string consisting of upper-case letters of the alphabet only and of minimum character-length 2…“, so you didn’t really need that IF clause, though it’s a nice touch in any case!
Also, I was wondering if you could somehow amend it so that the IFERROR is not required? Nothing wrong with such functions, though if we can do without then it’s generally preferable.
Thanks again, and congratulations on a correct solution!
Cheers!
@ikkeman
Apologies. Your post seems to have got lost in the ether! Only just seen it now.
Yes – nice amendment. I really should be using this construction more myself in place of ROW/INDIRECT. Of course, it should be pointed out that this alternative is not entirely non-volatile: constructions involving INDEX in this way, i.e. INDEX:INDEX are what is referred to as “volatile at workbook open only”.
Still, preferable to being “fully” volatile, as is INDIRECT. So thanks a lot – very nice contribution!
@diondan1
Likewise – apologies to you also for having somehow missed your post.
Another spot-on solution. Congratulations!
@Nhari
Hi and welcome to the site!
Another correct solution – well done!
Actually, I would just say that, if you’re using this approach then it’s not strictly necessary to use the CODE function. Simply:
would suffice, and be a bit shorter.
Thanks a lot!
@XOR LX
Thanks for the welcome and thanks for the suggestion.
=AND(MMULT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1))+{0,1},1)),{-1;1})>=0)@MichaelCH
Thanks a lot for joining us here and for your many excellent contributions. I understand that you don’t speak much English, and unfortunately I don’t speak any Russian, but hopefully we can translate any comments for the benefit of everyone.
By the way, some of these Formula Challenges to which you are now posting are quite old, and solutions and discussions can be found the following week. So for this challenge, for example, you may wish to read here:
https://excelxor.com/2015/02/18/advanced-formula-challenge-11-results-and-discussion/
in which you’ll find that my solution is almost identical to yours (although I prefer to not use the volatile INDIRECT for the ROW construction).
Many thanks and look forward to seeing more from you in future.
Regards
This is an old post. Hope it’s still OK. I had to try it.
Array entered.
Now I’ll look at the other posts.
I found I could shorten up my previous formula. IF was not necessary.
non-array entered.
=MIN(MMULT((ROW(OFFSET($A$1,,,LEN(A2)))=TRANSPOSE(ROW(OFFSET($A$1,,,LEN(A2)))))-(ROW(OFFSET($A$1,,,LEN(A2)))=1+TRANSPOSE(ROW(OFFSET($A$1,,,LEN(A2))))),INDEX({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26},MATCH(MID(A2,ROW(OFFSET($A$1,,,LEN(A2))),1),{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"}))))>=0