Original Blog Entry: Pick 4 Permutations In Excel

johnph77 — Sun, 10 May 2009 04:53:13 GMT

Stumbled across a question in the Pick 4 Forum the other day about inputting a 4-digit number in Excel and having the spreadsheet return all possible 24 permutations. I answered it but it was a PITA to formulate and edit my response. I thought about it for a while and came up with an easier way to construct the formulas. The Excel output is still the same no matter whether the formulas in my response in the Pick 4 Forum are used or copied from this entry. Here goes:

=(MID(A1,1,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,3,1)*10)+(MID(A1,4,1)*1)

=(MID(A1,1,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,4,1)*10)+(MID(A1,3,1)*1)

=(MID(A1,1,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,2,1)*10)+(MID(A1,4,1)*1)

=(MID(A1,1,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,4,1)*10)+(MID(A1,2,1)*1)

=(MID(A1,1,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,2,1)*10)+(MID(A1,3,1)*1)

=(MID(A1,1,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,3,1)*10)+(MID(A1,2,1)*1)

=(MID(A1,2,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,3,1)*10)+(MID(A1,4,1)*1)

=(MID(A1,2,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,4,1)*10)+(MID(A1,3,1)*1)

=(MID(A1,2,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,1,1)*10)+(MID(A1,4,1)*1)

=(MID(A1,2,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,4,1)*10)+(MID(A1,1,1)*1)

=(MID(A1,2,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,1,1)*10)+(MID(A1,3,1)*1)

=(MID(A1,2,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,3,1)*10)+(MID(A1,1,1)*1)

=(MID(A1,3,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,2,1)*10)+(MID(A1,4,1)*1)

=(MID(A1,3,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,4,1)*10)+(MID(A1,2,1)*1)

=(MID(A1,3,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,1,1)*10)+(MID(A1,4,1)*1)

=(MID(A1,3,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,4,1)*10)+(MID(A1,1,1)*1)

=(MID(A1,3,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,1,1)*10)+(MID(A1,2,1)*1)

=(MID(A1,3,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,2,1)*10)+(MID(A1,1,1)*1)

=(MID(A1,4,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,2,1)*10)+(MID(A1,3,1)*1)

=(MID(A1,4,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,3,1)*10)+(MID(A1,2,1)*1)

=(MID(A1,4,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,1,1)*10)+(MID(A1,3,1)*1)

=(MID(A1,4,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,3,1)*10)+(MID(A1,1,1)*1)

=(MID(A1,4,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,1,1)*10)+(MID(A1,2,1)*1)

=(MID(A1,4,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,2,1)*10)+(MID(A1,1,1)*1)

Enter each formula into individual spreadsheet cells where you would like to view each permutation.

The response in the Forum took about an hour to edit properly, the above was done in about 10 minutes in a text editor.

The formulas assume your initial 4-digit entry will be in Cell A1; if in any other place, the formula where A1 appears will need to be edited to your preferred entry location. The results of these formulas will be numeric - if text results are desired one may eliminate the *1000 , *100 , *10 and *1 portions of the formulas along with the outermost set of parentheses in each part of the formula - example:

=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)

gl

... [ More ]