Results 1 to 7 of 7

Thread: Which Excel formula can help to find 4 digit combination using the numbers 0 to 9

  1. #1
    Join Date
    Dec 2011
    Posts
    2

    Which Excel formula can help to find 4 digit combination using the numbers 0 to 9

    I need to create a formula that will show the results of all possible 4 digit combination using the numbers 0,1,2,3,4,5,6,7,8,9. I found a similar question on this forum but it was only for the numbers 0,1,3,4. Here is the formula I found but did not know how to edit it for my required results 0, 1, 2, 3, 4
    Code:
    =MOD(INT((ROW(A2)-1)/125),5)&MOD(INT((ROW(A2)-1)/25),5)&MOD(INT((ROW(A2)-1)/5),5)&MOD(ROW(A2)-1,5)

  2. #2
    Join Date
    Aug 2011
    Posts
    542

    Re: Which Excel formula can help to find 4 digit combination using the numbers0 to 9

    I've been trying to get my head around how may combination this is but it
    is so many Excel can't calculate it or at least i can't come up with a
    formula. having said that here's the formula you posted modified for all
    combination of 0 - 9
    Code:
    =MOD(INT((ROW(A2)-1)/9765625),5)&MOD(INT((ROW(A2)-1)/1953125),5)&MOD(INT((ROW(A2)-1)/390625),5)&MOD(INT((ROW(A2)-1)/78125),5)&MOD(INT((ROW(A2)-1)/15625),5)&MOD(INT((ROW(A2)-1)/3125),5)&MOD(INT((ROW(A2)-1)/625),5)&MOD(INT((ROW(A2)-1)/125),5)&MOD(INT((ROW(A2)-1)/25),5)&MOD(INT((ROW(A2)-1)/5),5)&MOD(ROW(A2)-1,5)
    BTW I ran out of rows in Excel 2007 and was nowhere near the end

  3. #3
    Join Date
    Aug 2011
    Posts
    700

    Re: Which Excel formula can help to find 4 digit combination using the numbers0 to 9

    Put this formula in A1:

    Code:
    =ROW(A1)-1
    and use a custom format on the cell of "0000" (without the quotes). Or alternatively use this formula:

    Code:
    =TEXT(ROW(A1)-1,"0000")
    and have the format as General. Then copy the down to A10000. Both formula will give you all the 4-digit numbers from 0000 to 9999.

  4. #4
    Join Date
    Aug 2011
    Posts
    568

    Re: Which Excel formula can help to find 4 digit combination using the numbers0 to 9

    Using the PERMUT fuction with 10 objects and 4 items I get 5040 possible. Using COMBIN function I get 210 possibles. Or am I missing something. Maybe I'm missing something -- given that we can have any 4 digits from the set of 10 (0-9), aren't there 10,000 numbers that can be created? 0000 through 9999 ?? Assuming each digit can be used more than once?

  5. #5
    Join Date
    Jul 2011
    Posts
    627

    Re: Which Excel formula can help to find 4 digit combination using the numbers0 to 9

    Or, just to make things more difficult, you can do it with a macro:
    Code:
    Sub Build4DigitNumsFrom10()
    Dim ThousandsLoop As Integer
    Dim HundredsLoop As Integer
    Dim TensLoop As Integer
    Dim OnesLoop As Integer
    Dim myNumber As Integer
    
    For ThousandsLoop = 0 To 9
    For HundredsLoop = 0 To 9
    For TensLoop = 0 To 9
    For OnesLoop = 0 To 9
    myNumber = ThousandsLoop * 1000 + _
    HundredsLoop * 100 + _
    TensLoop * 10 + OnesLoop
    ActiveSheet.Range("A" & myNumber + 1) = _
    myNumber
    ActiveSheet.Range("A" & myNumber + 1).NumberFormat = _
    "0000"
    Next ' ones loop
    Next ' tens loop
    Next ' hundreds loop
    Next ' thousands loop
    End Sub

  6. #6
    Join Date
    Jul 2011
    Posts
    638

    Re: Which Excel formula can help to find 4 digit combination using the numbers0 to 9

    I'm trying to do something similar. I have ten basketball players and I can only rotate out 5 at a time. How can I find all the possible combination of these ten players in five slots. Any and all help would be greatly appreciated.

    Using the PERMUT fuction with 10 objects and 4 items I get 5040 possible..
    Using COMBIN function I get 210 possibles.


    Or am I missing something?

  7. #7
    Join Date
    Jul 2011
    Posts
    642

    Re: Which Excel formula can help to find 4 digit combination using the numbers0 to 9

    I have the results of any 4 digits from 10; 1. 0 1 2 3

    1. 0 1 2 3
    2. 0 1 2 4
    3. 0 1 3 4
    4. 0 2 3 4
    5. 1 2 3 4
    6. 0 1 2 5
    7. 0 1 3 5
    8. 0 2 3 5
    9. 1 2 3 5
    10. 0 1 4 5
    11. 0 2 4 5
    12. 1 2 4 5
    13. 0 3 4 5
    14. 1 3 4 5
    15. 2 3 4 5
    16. 0 1 2 6
    17. 0 1 3 6
    18. 0 2 3 6
    19. 1 2 3 6
    20. 0 1 4 6
    21. 0 2 4 6
    22. 1 2 4 6
    23. 0 3 4 6
    24. 1 3 4 6
    25. 2 3 4 6
    26. 0 1 5 6
    27. 0 2 5 6
    28. 1 2 5 6
    29. 0 3 5 6
    30. 1 3 5 6
    31. 2 3 5 6
    32. 0 4 5 6
    33. 1 4 5 6
    34. 2 4 5 6
    35. 3 4 5 6
    36. 0 1 2 7
    37. 0 1 3 7
    38. 0 2 3 7
    39. 1 2 3 7
    40. 0 1 4 7
    41. 0 2 4 7
    42. 1 2 4 7
    43. 0 3 4 7
    44. 1 3 4 7
    45. 2 3 4 7
    46. 0 1 5 7
    47. 0 2 5 7
    48. 1 2 5 7
    49. 0 3 5 7
    50. 1 3 5 7
    51. 2 3 5 7
    52. 0 4 5 7
    53. 1 4 5 7
    54. 2 4 5 7
    55. 3 4 5 7
    56. 0 1 6 7
    57. 0 2 6 7
    58. 1 2 6 7
    59. 0 3 6 7
    60. 1 3 6 7
    61. 2 3 6 7
    62. 0 4 6 7
    63. 1 4 6 7
    64. 2 4 6 7
    65. 3 4 6 7
    66. 0 5 6 7
    67. 1 5 6 7
    68. 2 5 6 7
    69. 3 5 6 7
    70. 4 5 6 7
    71. 0 1 2 8
    72. 0 1 3 8
    73. 0 2 3 8
    74. 1 2 3 8
    75. 0 1 4 8
    76. 0 2 4 8
    77. 1 2 4 8
    78. 0 3 4 8
    79. 1 3 4 8
    80. 2 3 4 8
    81. 0 1 5 8
    82. 0 2 5 8
    83. 1 2 5 8
    84. 0 3 5 8
    85. 1 3 5 8
    86. 2 3 5 8
    87. 0 4 5 8
    88. 1 4 5 8
    89. 2 4 5 8
    90. 3 4 5 8
    91. 0 1 6 8
    92. 0 2 6 8
    93. 1 2 6 8
    94. 0 3 6 8
    95. 1 3 6 8
    96. 2 3 6 8
    97. 0 4 6 8
    98. 1 4 6 8
    99. 2 4 6 8
    100. 3 4 6 8
    101. 0 5 6 8
    102. 1 5 6 8
    103. 2 5 6 8
    104. 3 5 6 8
    105. 4 5 6 8
    106. 0 1 7 8
    107. 0 2 7 8
    108. 1 2 7 8
    109. 0 3 7 8
    110. 1 3 7 8
    111. 2 3 7 8
    112. 0 4 7 8
    113. 1 4 7 8
    114. 2 4 7 8
    115. 3 4 7 8
    116. 0 5 7 8
    117. 1 5 7 8
    118. 2 5 7 8
    119. 3 5 7 8
    120. 4 5 7 8
    121. 0 6 7 8
    122. 1 6 7 8
    123. 2 6 7 8
    124. 3 6 7 8
    125. 4 6 7 8
    126. 5 6 7 8
    127. 0 1 2 9
    128. 0 1 3 9
    129. 0 2 3 9
    130. 1 2 3 9
    131. 0 1 4 9
    132. 0 2 4 9
    133. 1 2 4 9
    134. 0 3 4 9
    135. 1 3 4 9
    136. 2 3 4 9
    137. 0 1 5 9
    138. 0 2 5 9
    139. 1 2 5 9
    140. 0 3 5 9
    141. 1 3 5 9
    142. 2 3 5 9
    143. 0 4 5 9
    144. 1 4 5 9
    145. 2 4 5 9
    146. 3 4 5 9
    147. 0 1 6 9
    148. 0 2 6 9
    149. 1 2 6 9
    150. 0 3 6 9
    151. 1 3 6 9
    152. 2 3 6 9
    153. 0 4 6 9
    154. 1 4 6 9
    155. 2 4 6 9
    156. 3 4 6 9
    157. 0 5 6 9
    158. 1 5 6 9
    159. 2 5 6 9
    160. 3 5 6 9
    161. 4 5 6 9
    162. 0 1 7 9
    163. 0 2 7 9
    164. 1 2 7 9
    165. 0 3 7 9
    166. 1 3 7 9
    167. 2 3 7 9
    168. 0 4 7 9
    169. 1 4 7 9
    170. 2 4 7 9
    171. 3 4 7 9
    172. 0 5 7 9
    173. 1 5 7 9
    174. 2 5 7 9
    175. 3 5 7 9
    176. 4 5 7 9
    177. 0 6 7 9
    178. 1 6 7 9
    179. 2 6 7 9
    180. 3 6 7 9
    181. 4 6 7 9
    182. 5 6 7 9
    183. 0 1 8 9
    184. 0 2 8 9
    185. 1 2 8 9
    186. 0 3 8 9
    187. 1 3 8 9
    188. 2 3 8 9
    189. 0 4 8 9
    190. 1 4 8 9
    191. 2 4 8 9
    192. 3 4 8 9
    193. 0 5 8 9
    194. 1 5 8 9
    195. 2 5 8 9
    196. 3 5 8 9
    197. 4 5 8 9
    198. 0 6 8 9
    199. 1 6 8 9
    200. 2 6 8 9
    201. 3 6 8 9
    202. 4 6 8 9
    203. 5 6 8 9
    204. 0 7 8 9
    205. 1 7 8 9
    206. 2 7 8 9
    207. 3 7 8 9
    208. 4 7 8 9
    209. 5 7 8 9
    210. 6 7 8 9

Similar Threads

  1. How to calculate told of numbers combination in Microsoft Excel list
    By Ankit Zh@very in forum MS Office Support
    Replies: 3
    Last Post: 24-01-2012, 01:23 PM
  2. How to find frequency of lottery numbers in Microsoft Excel
    By Aarti C in forum MS Office Support
    Replies: 1
    Last Post: 14-01-2012, 12:52 PM
  3. Siri doesn't recognize 3 Digit Phone Numbers
    By Bhaisora in forum Portable Devices
    Replies: 6
    Last Post: 21-12-2011, 05:12 AM
  4. Formula to find next value in row in MS Excel
    By Abhiraj in forum Windows Software
    Replies: 2
    Last Post: 12-06-2009, 12:57 PM
  5. CDMA, GSM Mobile Phones to Have 11 Digit Numbers soon..
    By Samsher in forum India BroadBand
    Replies: 2
    Last Post: 05-05-2009, 10:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •