Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Formula to find all possible 4 digit combinations using the numbers0,1,2,3,4,5,6,7,8,9

Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Formula to find all possible 4 digit combinations using the numbers0,1,2,3,4,5,6,7,8,9

Hi there!

To generate all possible 4 digit combinations using the numbers 0-9, you can use the following
Code:
formula
:

Code:
=TEXT(ROW(A1)-1,"0000")
This formula uses the ROW function to generate a series of numbers starting from 1, and then subtracts 1 to start the series from 0. The TEXT function is then used to format the numbers as 4 digits with leading zeros.

You can follow these steps to generate all possible combinations:
  1. Enter the formula in cell A1.
  2. Copy the formula down as many rows as you need to generate all possible combinations.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Formula to find all possible 4 digit combinations using the number

Danelle,

I've been trying to get my head around how may combinations 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
combinations of 0 - 9

=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

Mike

"Danelle Sanchez" wrote:

Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Formula to find all possible 4 digit combinations using thenumbers 0,1,2,3,4,5,6,7,8,9

Put this formula in A1:

=ROW(A1)-1

and use a custom format on the cell of "0000" (without the quotes).

Or alternatively use this formula:

=TEXT(ROW(A1)-1,"0000")

and have the format as General.

Then copy the down to A10000.

Both formulae will give you all the 4-digit numbers from 0000 to 9999.

Hope this helps.

Pete

On Sep 22, 11:19*pm, Danelle Sanchez wrote:
Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Patternhttp://www.eggheadcafe.com/tutorials/aspnet/ec832ac7-6e4c-4ea8-81ab-7...


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Formula to find all possible 4 digit combinations using the number

Mike

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?


Gord Dibben MS Excel MVP

On Tue, 22 Sep 2009 16:20:01 -0700, Mike H
wrote:

Danelle,

I've been trying to get my head around how may combinations 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
combinations of 0 - 9

=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

Mike

"Danelle Sanchez" wrote:

Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,203
Default Formula to find all possible 4 digit combinations using the nu

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?


"Gord Dibben" wrote:

Mike

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?


Gord Dibben MS Excel MVP

On Tue, 22 Sep 2009 16:20:01 -0700, Mike H
wrote:

Danelle,

I've been trying to get my head around how may combinations 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
combinations of 0 - 9

=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

Mike

"Danelle Sanchez" wrote:

Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,203
Default Formula to find all possible 4 digit combinations using the nu

Or, just to make things more difficult, you can do it with a macro:

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

"Pete_UK" wrote:

Put this formula in A1:

=ROW(A1)-1

and use a custom format on the cell of "0000" (without the quotes).

Or alternatively use this formula:

=TEXT(ROW(A1)-1,"0000")

and have the format as General.

Then copy the down to A10000.

Both formulae will give you all the 4-digit numbers from 0000 to 9999.

Hope this helps.

Pete

On Sep 22, 11:19 pm, Danelle Sanchez wrote:
Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Patternhttp://www.eggheadcafe.com/tutorials/aspnet/ec832ac7-6e4c-4ea8-81ab-7...



  #8   Report Post  
Member
 
Posts: 89
Default

Gord,
I'm trying to do something similiar. I have ten basketball players and I can only rotate out 5 at a time. How can I find all the possible combinations 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?


Gord Dibben MS Excel MVP

On Tue, 22 Sep 2009 16:20:01 -0700, Mike H
wrote:

Danelle,

I've been trying to get my head around how may combinations 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
combinations of 0 - 9

=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

Mike

"Danelle Sanchez" wrote:

Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx
[/quote]
  #9   Report Post  
Junior Member
 
Posts: 3
Default

I was searching for crucial information on this subject.
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Formula to find all possible 4 digit combinations using the numbers0,1,2,3,4,5,6,7,8,9

I have the results of
any 4 digits from 10; 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


On Tuesday, September 22, 2009 6:19 PM Danelle Sanchez wrote:


Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx



On Tuesday, September 22, 2009 7:20 PM Mike H wrote:


Danelle,

I have been trying to get my head around how may combinations this is but it
is so many Excel cannot calculate it or at least i cannot come up with a
formula. having said that here is the formula you posted modified for all
combinations of 0 - 9

=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

Mike

"Danelle Sanchez" wrote:



On Tuesday, September 22, 2009 8:08 PM Gord Dibben wrote:


Mike

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?


Gord Dibben MS Excel MVP

wrote:



On Wednesday, September 23, 2009 1:37 AM JLatham wrote:


Maybe I am missing something -- given that we can have any 4 digits from the
set of 10 (0-9), are not there 10,000 numbers that can be created? 0000
through 9999 ?? Assuming each digit can be used more than once?


"Gord Dibben" wrote:



On Wednesday, September 23, 2009 1:45 AM JLatham wrote:


Or, just to make things more difficult, you can do it with a macro:

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

"Pete_UK" wrote:



On Friday, September 25, 2009 2:22 PM Pete_UK wrote:


Put this formula in A1:

=3DROW(A1)-1

and use a custom format on the cell of "0000" (without the quotes).

Or alternatively use this formula:

=3DTEXT(ROW(A1)-1,"0000")

and have the format as General.

Then copy the down to A10000.

Both formulae will give you all the 4-digit numbers from 0000 to 9999.

Hope this helps.

Pete

igit combinations 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. =A0
ed results
)-1)=AD/5),5)&MOD(ROW(A2)-1,5)
ials/aspnet/ec832ac7-6e4c-4ea8-81ab-7...



On Monday, January 24, 2011 3:09 PM lyndsey wilson wrote:


Hi

I have a similar problem. I need to find all 4 digit combinations of numbers 1-5 but not sure which parts of the above suggestions to change. I tried the excel thing but couldnt get it to work as printed...

Can anyone help me please?

Thanks






  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Formula to find all possible 4 digit combinations using the numbers0,1,2,3,4,5,6,7,8,9

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


On Tuesday, September 22, 2009 6:19 PM Danelle Sanchez wrote:


Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx



On Tuesday, September 22, 2009 7:20 PM Mike H wrote:


Danelle,

I have been trying to get my head around how may combinations this is but it
is so many Excel cannot calculate it or at least i cannot come up with a
formula. having said that here is the formula you posted modified for all
combinations of 0 - 9

=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

Mike

"Danelle Sanchez" wrote:



On Tuesday, September 22, 2009 8:08 PM Gord Dibben wrote:


Mike

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?


Gord Dibben MS Excel MVP

wrote:



On Wednesday, September 23, 2009 1:37 AM JLatham wrote:


Maybe I am missing something -- given that we can have any 4 digits from the
set of 10 (0-9), are not there 10,000 numbers that can be created? 0000
through 9999 ?? Assuming each digit can be used more than once?


"Gord Dibben" wrote:



On Wednesday, September 23, 2009 1:45 AM JLatham wrote:


Or, just to make things more difficult, you can do it with a macro:

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

"Pete_UK" wrote:



On Friday, September 25, 2009 2:22 PM Pete_UK wrote:


Put this formula in A1:

=3DROW(A1)-1

and use a custom format on the cell of "0000" (without the quotes).

Or alternatively use this formula:

=3DTEXT(ROW(A1)-1,"0000")

and have the format as General.

Then copy the down to A10000.

Both formulae will give you all the 4-digit numbers from 0000 to 9999.

Hope this helps.

Pete

igit combinations 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. =A0
ed results
)-1)=AD/5),5)&MOD(ROW(A2)-1,5)
ials/aspnet/ec832ac7-6e4c-4ea8-81ab-7...



On Monday, January 24, 2011 3:09 PM lyndsey wilson wrote:


Hi

I have a similar problem. I need to find all 4 digit combinations of numbers 1-5 but not sure which parts of the above suggestions to change. I tried the excel thing but couldnt get it to work as printed...

Can anyone help me please?

Thanks



On Monday, April 25, 2011 6:04 AM mehmet wrote:


I have the results of

any 4 digits from 10; 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




  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Formula to find all possible 4 digit combinations using the numbers0,1,2,3,4,5,6,7,8,9

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


On Tuesday, September 22, 2009 6:19 PM Danelle Sanchez wrote:


Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx



On Tuesday, September 22, 2009 7:20 PM Mike H wrote:


Danelle,

I have been trying to get my head around how may combinations this is but it
is so many Excel cannot calculate it or at least i cannot come up with a
formula. having said that here is the formula you posted modified for all
combinations of 0 - 9

=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

Mike

"Danelle Sanchez" wrote:



On Tuesday, September 22, 2009 8:08 PM Gord Dibben wrote:


Mike

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?


Gord Dibben MS Excel MVP

wrote:



On Wednesday, September 23, 2009 1:37 AM JLatham wrote:


Maybe I am missing something -- given that we can have any 4 digits from the
set of 10 (0-9), are not there 10,000 numbers that can be created? 0000
through 9999 ?? Assuming each digit can be used more than once?


"Gord Dibben" wrote:



On Wednesday, September 23, 2009 1:45 AM JLatham wrote:


Or, just to make things more difficult, you can do it with a macro:

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

"Pete_UK" wrote:



On Friday, September 25, 2009 2:22 PM Pete_UK wrote:


Put this formula in A1:

=3DROW(A1)-1

and use a custom format on the cell of "0000" (without the quotes).

Or alternatively use this formula:

=3DTEXT(ROW(A1)-1,"0000")

and have the format as General.

Then copy the down to A10000.

Both formulae will give you all the 4-digit numbers from 0000 to 9999.

Hope this helps.

Pete

igit combinations 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. =A0
ed results
)-1)=AD/5),5)&MOD(ROW(A2)-1,5)
ials/aspnet/ec832ac7-6e4c-4ea8-81ab-7...



On Monday, January 24, 2011 3:09 PM lyndsey wilson wrote:


Hi

I have a similar problem. I need to find all 4 digit combinations of numbers 1-5 but not sure which parts of the above suggestions to change. I tried the excel thing but couldnt get it to work as printed...

Can anyone help me please?

Thanks



On Monday, April 25, 2011 6:04 AM mehmet wrote:


I have the results of

any 4 digits from 10; 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



On Monday, April 25, 2011 6:04 AM mehmet wrote:


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




  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Formula to find all possible 4 digit combinations using thenumbers 0,1,2,3,4,5,6,7,8,9

hi Pete, i'm having a similar issue trying to find all the 4 digit combinations from the numbers 1 through 6.

pasting the formula you gave below for 1-5 works great, but i'm not sure how to adjust it to get to six.

do you mind helping?! thanks!!!
- hal


On Monday, January 24, 2011 at 6:26:26 PM UTC-6, Pete_UK wrote:
Lyndsey,

put this formula in A1 of a blank sheet:

=MOD(1000*(INT((ROW(A1)-1)/125)),5000)+1000+MOD(100*(INT((ROW(A1)-1)/
25)),500)+100+MOD(10*(INT((ROW(A1)-1)/5)),50)+10+MOD(ROW(A1)-1,5)+1

and copy down to A625 (after which it repeats). This is all one
formula, so be wary of spurious line breaks. I've repeated it below so
that you can see the 4 elements making up each digit:

=MOD(1000*(INT((ROW(A1)-1)/125)),5000)+1000
+MOD(100*(INT((ROW(A1)-1)/25)),500)+100
+MOD(10*(INT((ROW(A1)-1)/5)),50)+10
+MOD(ROW(A1)-1,5)+1

Hope this helps.

Pete

  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Formula to find all possible 4 digit combinations using thenumbers 0,1,2,3,4,5,6,7,8,9

On Tuesday, September 22, 2009 at 5:19:17 PM UTC-5, wrote:
Hi,
I need to create a formula that will show the results of all possible 4 digit combinations 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

=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)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I find all possible combinations of words? Yvonne Excel Worksheet Functions 4 June 6th 08 01:19 AM
formula to find all possible combinations maijiuli Excel Worksheet Functions 2 July 12th 07 10:22 PM
how can i find all possible combinations nomi Excel Discussion (Misc queries) 3 February 13th 06 01:33 PM
find all combinations of cells that add up to certain number AD Excel Worksheet Functions 1 November 17th 05 08:50 PM
Find combinations of numbers to equal a value jubu Excel Worksheet Functions 1 March 18th 05 01:46 PM


All times are GMT +1. The time now is 05:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"