listing all 243 possible outcomes of five soccer matches

[kobbymoe]

will like to know how to generate all possible outcomes from five different soccer matches. its 3^5 = 243 outcomes but will like the list of all the outcomes as in HHH, HWW...... in that order. not really good in excel consider me as a newbie and break thing down for me

 

[oldbrewer]

WWWWW					3	D			1	W
WWWWL					3	D			2	L
WWWWD					3	D			3	D
WWWLW					3	D
WWWLL					3	D	DDDDD
WWWLD
WWWDW
WWWDL
WWWDD
WWLWW
WWLWL							this macro generates the list
WWLWD
WWLLW							For a = 1 To 3
WWLLL							Cells(1, 6) = a
WWLLD							For b = 1 To 3
WWLDW							Cells(2, 6) = b
WWLDL							For c = 1 To 3
WWLDD							Cells(3, 6) = c
WWDWW							For d = 1 To 3
WWDWL							Cells(4, 6) = d
WWDWD							For e = 1 To 3
WWDLW							Cells(5, 6) = e
WWDLL							rrow = rrow + 1
WWDLD							Cells(rrow, 1) = Cells(5, 8)
WWDDW							Next e
WWDDL							Next d
WWDDD							Next c
WLWWW							Next b
WLWWL							Next a
WLWWD							End Sub
WLWLW
WLWLL
WLWLD
WLWDW
WLWDL
WLWDD						I have only shown the first 40 permutations to save space
WLLWW
WLLWL
WLLWD

<colgroup><col><col span="11"></colgroup><tbody>
</tbody>

 

[kobbymoe]

thanks but need further explanation

hello oldbrewer, thanks a lot for that, im sorry for being dumb but i dont really get it as in how im going to input the formulas to get what i want . dont know if i can ask you to just list all the outcomes for me so i get satisfied and go away with my troubles

 

[oldbrewer]

Re: thanks but need further explanation

WWWWW	WLWWW	WDWWW	LWWWW	LLWWW	LDWWW	DWWWW	DLWWW	DDWWW
WWWWL	WLWWL	WDWWL	LWWWL	LLWWL	LDWWL	DWWWL	DLWWL	DDWWL
WWWWD	WLWWD	WDWWD	LWWWD	LLWWD	LDWWD	DWWWD	DLWWD	DDWWD
WWWLW	WLWLW	WDWLW	LWWLW	LLWLW	LDWLW	DWWLW	DLWLW	DDWLW
WWWLL	WLWLL	WDWLL	LWWLL	LLWLL	LDWLL	DWWLL	DLWLL	DDWLL
WWWLD	WLWLD	WDWLD	LWWLD	LLWLD	LDWLD	DWWLD	DLWLD	DDWLD
WWWDW	WLWDW	WDWDW	LWWDW	LLWDW	LDWDW	DWWDW	DLWDW	DDWDW
WWWDL	WLWDL	WDWDL	LWWDL	LLWDL	LDWDL	DWWDL	DLWDL	DDWDL
WWWDD	WLWDD	WDWDD	LWWDD	LLWDD	LDWDD	DWWDD	DLWDD	DDWDD
WWLWW	WLLWW	WDLWW	LWLWW	LLLWW	LDLWW	DWLWW	DLLWW	DDLWW
WWLWL	WLLWL	WDLWL	LWLWL	LLLWL	LDLWL	DWLWL	DLLWL	DDLWL
WWLWD	WLLWD	WDLWD	LWLWD	LLLWD	LDLWD	DWLWD	DLLWD	DDLWD
WWLLW	WLLLW	WDLLW	LWLLW	LLLLW	LDLLW	DWLLW	DLLLW	DDLLW
WWLLL	WLLLL	WDLLL	LWLLL	LLLLL	LDLLL	DWLLL	DLLLL	DDLLL
WWLLD	WLLLD	WDLLD	LWLLD	LLLLD	LDLLD	DWLLD	DLLLD	DDLLD
WWLDW	WLLDW	WDLDW	LWLDW	LLLDW	LDLDW	DWLDW	DLLDW	DDLDW
WWLDL	WLLDL	WDLDL	LWLDL	LLLDL	LDLDL	DWLDL	DLLDL	DDLDL
WWLDD	WLLDD	WDLDD	LWLDD	LLLDD	LDLDD	DWLDD	DLLDD	DDLDD
WWDWW	WLDWW	WDDWW	LWDWW	LLDWW	LDDWW	DWDWW	DLDWW	DDDWW
WWDWL	WLDWL	WDDWL	LWDWL	LLDWL	LDDWL	DWDWL	DLDWL	DDDWL
WWDWD	WLDWD	WDDWD	LWDWD	LLDWD	LDDWD	DWDWD	DLDWD	DDDWD
WWDLW	WLDLW	WDDLW	LWDLW	LLDLW	LDDLW	DWDLW	DLDLW	DDDLW
WWDLL	WLDLL	WDDLL	LWDLL	LLDLL	LDDLL	DWDLL	DLDLL	DDDLL
WWDLD	WLDLD	WDDLD	LWDLD	LLDLD	LDDLD	DWDLD	DLDLD	DDDLD
WWDDW	WLDDW	WDDDW	LWDDW	LLDDW	LDDDW	DWDDW	DLDDW	DDDDW
WWDDL	WLDDL	WDDDL	LWDDL	LLDDL	LDDDL	DWDDL	DLDDL	DDDDL
WWDDD	WLDDD	WDDDD	LWDDD	LLDDD	LDDDD	DWDDD	DLDDD	DDDDD

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Ruddles]

Put this formula in A1:-

=MID(REPT("WDL",81),INT((ROW()-1)/81+1),1)&MID(REPT("WDL",81),INT((ROW()-1)/27+1),1)&MID(REPT("WDL",81),INT((ROW()-1)/9+1),1)&MID(REPT("WDL",81),INT((ROW()-1)/3+1),1)&MID("WDL",MOD(ROW()-1,3)+1,1)

Now copy it down to A2:A243. Is that what you're trying to do?

 

[Ruddles]

Or you could use separate cells:-

A1: =MID(REPT("WDL",81),INT((ROW()-1)/81+1),1)
B1: =MID(REPT("WDL",81),INT((ROW()-1)/27+1),1)
C1: =MID(REPT("WDL",81),INT((ROW()-1)/9+1),1)
D1: =MID(REPT("WDL",81),INT((ROW()-1)/3+1),1)
E1: =MID("WDL",MOD(ROW()-1,3)+1,1)

And then copy A1:E1 down to A2:E243.

How about that?

 

[kobbymoe]

Re: thanks but need further explanation

thanks a lot man .. you have no idea what you have done for me.. thanks very very much

 

[kobbymoe]

Re: thanks but need further explanation

so much thankful

 

[mikerickson]

Re: thanks but need further explanation

Put WWWWW in A1
Put
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,4)&MID("LD@",SEARCH(RIGHT(A1,1),"WLD"),1),"D@","@W"),"D@","@W"),"D@","@W"),"W@","LW"),"L@","DW")

In A2 and drag down

 

[mikerickson]

Re: thanks but need further explanation

Or, define names
Name: Power3 RefersTo: =3^{0,1,2,3,4}
Name: Power10 RefersTo: =10^{0,1,2,3,4}

and put this in A1 and drag down.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUMPRODUCT(MOD(CEILING(ROW(A1),Power3)/Power3,3)*power10),"0","D"),"1","W"),"2","L")