-1

I need help with developing an Excel formula that determines whether or not a 4-digit number has "stacking" digits (digits that are higher than the last e.g. 1234, 2356, 5679) without any duplicates of the digits. For instance, if I type in 4567 in a cell, then the formula would suggest that this is a "stacking" number through a 0 or 1 output (0 = no, 1 = yes). This is with the assumption that digits range from 0-9 and the first digit can be a zero. I've been searching for solutions for a while but unfortunately I have not found any.

If you can also find a formula that does the same thing but for 4-digit numbers with 3 recurring digits and 1 stacking digit (e.g. 3334, 7779, 4555), or 2 recurring digits with 2 stacking digits (e.g. 2234, 3455, 4556), or 2 pairs of recurring digits that do stack (e.g. 3344, 6688, 2288) then please also let me know.

2
  • 4
    Instead of searching, have you tried writing a formula, and testing it? – Michael Harvey May 8 at 7:51
  • 3
    Or write a VBA macro? Or use a scripting language? – Michael Harvey May 8 at 7:52
1

If you have Office 365, you can use:

=AND(--MID(L1,SEQUENCE(LEN(L1)-1),1)<--MID(L1,SEQUENCE(LEN(L1)-1,,2),1))

or

=AND(--MID(L1,SEQUENCE(LEN(L1)-1),1)<=--MID(L1,SEQUENCE(LEN(L1)-1,,2),1))

The first will show TRUE for stacked digits that do not repeat.
The second for stacked digits that may repeat.

If you don't have the SEQUENCE function you can use:

=AND(--MID(L1,ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(L1)-1)),1)<--MID(L1,ROW(INDEX($A:$A,2):INDEX($A:$A,LEN(L1))),1))

=AND(--MID(L1,ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(L1)-1)),1)<=--MID(L1,ROW(INDEX($A:$A,2):INDEX($A:$A,LEN(L1))),1))

Algorithm

  • MID(L1,SEQUENCE(LEN(L1)-1),1 returns an array of the first N-1 characters
  • MID(L1,SEQUENCE(LEN(L1)-1,,2),1)) returns an array of the last N-1 characters
  • So to detect the kinds of patterns you are looking for, it is merely a matter of comparing the two arrays.

Using the LET function since the logic is more clear:

Stacked digits only

=LET(x,--MID(L1,SEQUENCE(LEN(L1)-1),1),
     y,--MID(L1,SEQUENCE(LEN(L1)-1,,2),1),
     AND(x<y))

Stacked with or without pairs
Could be all the same

=LET(x,--MID(L1,SEQUENCE(LEN(L1)-1),1),
     y,--MID(L1,SEQUENCE(LEN(L1)-1,,2),1),
     AND(x<=y))

Stacked but MUST have at least one pair
Exclude all the same

=LET(x,--MID(L2,SEQUENCE(LEN(L2)-1),1),
           y,--MID(L2,SEQUENCE(LEN(L2)-1,,2),1),
           AND(x<=y,OR(x=y),OR(x<y)))

And I'm sure you can think of other variations.

enter image description here

9
  • Thank you so much! You're a lifesaver. I want to ask one final favour though, your =AND(--MID(L1,SEQUENCE(LEN(L1)-1),1)<=--MID(L1,SEQUENCE(LEN(L1)-1,,2),1)) formula for stacked digits that may repeat, also takes into account non-repeating numbers. Is there a way to remove this? – Jason Lover May 9 at 8:12
  • @JasonLover See my edit. – Ron Rosenfeld May 9 at 10:11
  • @JasonLover If my answer is what you require, kindly mark it as Accepted. See What should I do when someone answers my question. – Ron Rosenfeld May 10 at 10:41
  • I've encountered problems using the stacks only formula. When numbers are 0001-0009, the equation comes out with a "#CALC!" error since there's only 1 digit. Initially I tried to make the "#CALC!" error appear as FALSE with the IF function since it is 3 recurring 0s, but this didn't work. How do you remove this error? – Jason Lover May 10 at 23:19
  • @JasonLover That is because you are storing those values as Numbers. The Number 0001 is, arithmetically, the number 1 and that is how Excel stores numeric data. If you want to treat values with leading zero's as text, you will need to enter them as text. You can do this either by pre-formatting the cell as Text, or by preceding the string of digits with a single quote. (The single quote is a prefixCharacter and will not appear in the cell). You could also use the TEXT function, but that seems like overkill. – Ron Rosenfeld May 11 at 0:59
0

Here is a brute force formula, verifying the total length and comparing each digit sequentially:

=(LEN(A1)=4)*(--LEFT(A1,1)<--MID(A1,2,1))*(--MID(A1,2,1)<--MID(A1,3,1))*(--MID(A1,3,1)<RIGHT(A1,1))

enter image description here

Multiplying the Booleans is equivalent to using an AND() expression.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.