# Is there an excel formula that determines whether a number is “stacking” or not?

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.

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

## 2 Answers

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

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))
`````` Multiplying the Booleans is equivalent to using an AND() expression.