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!

28 comments

  1. 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!

  2. @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

  3. Here is a solution for Google Sheets:

    =A2=join(,sort(transpose(split(regexreplace(A2,,"."),"."))))
  4. =AND(MID(A6,ROW(INDIRECT("1:"&LEN(A6)-1))+1,1)>=MID(A6,ROW(INDIRECT("1:"&LEN(A6)-1)),1))

    CSE formula

  5. =AND(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1))+1,1)>=MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1))

    Array formula

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

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

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

  9. getting rid of the volatile indirect function

    CSE

    =AND(MID(A16,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A16)-1)),1)<=MID(A16,ROW(INDEX(A:A,2):INDEX(A:A,LEN(A16))),1))
  10. @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

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

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

  13. Almost similar to @Oscars answer. Just added the 2 criteria check and offset function.

    =IF(AND(LEN(A2)>1,CODE(A2)<91),MID(A2,ROW(OFFSET($H$1,,,LEN(A2))),1)<=MID(A2,ROW(OFFSET($H$1,,,LEN(A2)))+1,1),"Alphabets in Lower Case")

    Learnt something new today! Thanks for the challenge @XORLX

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

  15. Oops! I guess my brain wasn’t working quite well!

    This is the corrected version:

    =IF(AND(LEN(A2)>1,CODE(A2)<91),AND(CODE(MID(A2,ROW(OFFSET($H$1,,,LEN(A2))),1))<=IFERROR(CODE(MID(A2,ROW(OFFSET($H$1,,,LEN(A2)))+1,1)),"")),"Alphabets in Lower Case/Less than 2 characters")

    Thanks @XORLX

  16. 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!

  17. @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!

  18. =AND((CODE(MID(A2,ROW(OFFSET($A$2,,,LEN(A2)-1)),1))-CODE(MID(A2,ROW(OFFSET($A$1,,,LEN(A2)-1)),1)))>=0)
  19. @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:

    =AND(MID(A2,ROW(OFFSET($A$2,,,LEN(A2)-1)),1)>=MID(A2,ROW(OFFSET($A$1,,,LEN(A2)-1)),1))

    would suffice, and be a bit shorter.

    Thanks a lot!

  20. @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

  21. This is an old post. Hope it’s still OK. I had to try it.

    Array entered.

    =(MATCH(1,IF(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)>MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))+1,1),1),0)=LEN(A2))

    Now I’ll look at the other posts.

  22. I found I could shorten up my previous formula. IF was not necessary.

    non-array entered.

    =MATCH(1,INDEX(--(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)>MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))+1,1)),0),0)=LEN(A2)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s