Generate Combination Lock & Zero-Padded PIN Codes (SEQUENCE & MOD)

Count states and decode a three-dial example

For N independent positions, each with B possible symbols and repetition allowed, there are B^N ordered states. This is a mathematical enumeration, not a description of the turning procedure or permitted combinations of a particular physical lock. Predictable enumeration is not a method for generating secure random PINs.

The practice sheet uses base B in B2, dial count N in B3 and a one-based code index k in B4. With B=40, N=3 and k=1686, the zero-based index is 1685 = 1×1600 + 2×40 + 5, so the tuple is 1-2-5. The rightmost position changes fastest.

The count example deliberately accepts integer bases 2..100 and dial counts 1..6, keeping its largest count at 10^12. These are teaching-example limits, not universal spreadsheet limits. Counting states does not allocate a row for each state. C2 displays the count; C4 displays the tuple.

=IF(COUNT(B2:B3)<>2,"Enter numeric base and dial count",IF(OR(B2<2,B2>100,B2<>INT(B2),B3<1,B3>6,B3<>INT(B3)),"Use integer base 2..100 and dial count 1..6",B2^B3))

Decode the k-th three-dial state

This decoder specifically requires N=3. Changing N changes the count, but requires a different decoder; the formula rejects other dial counts rather than returning a misleading three-part code.

For n=k−1, the three positions are QUOTIENT(n,B^2), MOD(QUOTIENT(n,B),B), and MOD(n,B). Index 1 gives 0-0-0; index 64000 gives 39-39-39 for base 40. Fractional, missing and out-of-range inputs are rejected.

=IF(COUNT(B2:B4)<>3,"Enter numeric base, dial count and index",IF(OR(B2<2,B2>100,B2<>INT(B2),B3<>3,B4<>INT(B4)),"Use integer base 2..100, three dials and integer index",IF(OR(B4<1,B4>B2^3),"Index Out of Range",QUOTIENT(B4-1,B2^2)&"-"&MOD(QUOTIENT(B4-1,B2),B2)&"-"&MOD(B4-1,B2))))

Format a separate four-digit decimal PIN index

E2 is a separate one-based decimal PIN index, independent of the base-40 lock index in B4. F2 contains the formula below. E2=43 produces the text 0042; 1 produces 0000 and 10000 produces 9999.

The range check matters: RIGHT alone would silently truncate code 10000 to 0000, creating a duplicate. Preserve the result as text when moving it to another application; automatic numeric conversion can remove leading zeros.

=IF(NOT(ISNUMBER(E2)),"Enter numeric PIN index",IF(OR(E2<>INT(E2),E2<1,E2>10000),"Use integer PIN index 1..10000",RIGHT("0000"&(E2-1),4)))

Generate all 10,000 four-digit codes

In a new empty sheet, enter the formula below in A2. It spills the numeric sequence 0 through 9999 into A2:A10001. Leave that destination empty. In B2 enter =RIGHT("0000"&A2,4), then fill down through B10001. Column B contains the 10,000 distinct text codes 0000 through 9999.

To try a small list first, replace 10000 with 10. The practice sheet includes that ten-row preview in H2:H11 and its text equivalents in I2:I11. H2 contains the SEQUENCE formula; each I row formats its corresponding H cell.

The scalar RIGHT wrapper does not expand over a SEQUENCE array in the current Arte Sheets engine. Use the separate filled-down text column rather than assuming =RIGHT("0000"&SEQUENCE(10000,1,0),4) spills correctly here.

Do not expand B^N rows for arbitrary bases: even 40^3 creates 64,000 rows and larger spaces quickly exceed practical memory or worksheet limits. The indexed decoder lets you examine a state without generating the whole space.

=SEQUENCE(10000,1,0,1)

Further reading

• Microsoft Support — QUOTIENT function
• Microsoft Support — MOD function
• Microsoft Support — SEQUENCE function