Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



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

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 31-12-2011
Member
 
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)

Reply With Quote
  #2  
Old 31-12-2011
Member
 
Join Date: Aug 2011
Posts: 534
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
Reply With Quote
  #3  
Old 31-12-2011
Member
 
Join Date: Aug 2011
Posts: 683
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.
Reply With Quote
  #4  
Old 31-12-2011
Member
 
Join Date: Aug 2011
Posts: 560
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?
Reply With Quote
  #5  
Old 31-12-2011
Member
 
Join Date: Jul 2011
Posts: 612
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
Reply With Quote
  #6  
Old 31-12-2011
Member
 
Join Date: Jul 2011
Posts: 623
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?
Reply With Quote
  #7  
Old 31-12-2011
Member
 
Join Date: Jul 2011
Posts: 627
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
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


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


All times are GMT +5.5. The time now is 06:40 PM.