How can I create a matrix of 300 x 300 automatically in excel

[Abdmujib]

The Picture attached is a matrix of 10 X 10 (10 columns and 10 rows). I did it manually then and it was tedious. there is now a need to do at least 300 x 300. Pls is there a way to get it done easily without doing it manually.

Thanks




Note: The earlier post got deleted and I didn't understand the reason (Was deleted reasons: pls do not bump your tread with 24hrs, as it lessen your chance of getting help).

 

[Abdmujib]

I'm not sure what you mean by function?

You could do this entirely in VBA, and dump just the results to Excel, i.e. don't use Excel formulae.

Or you could use VBA to create the Excel formulae, which is what I've done below:

Before code:

	A	B	C	D
1		Lambdas
2		Home Team	1.1
3		Away Team	0.9
4
5
6
7
Sheet1

After code:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Lambdas
2		Home Team	1.1
3		Away Team	0.9
4
5		Total	100.00
6
7			Away
8		Home	0	1	2	3	4	5	6	7	8	9	10
9		0	13.53	12.18	5.48	1.64	0.37	0.07	0.01	0.00	0.00	0.00	0.00
10		1	14.89	13.40	6.03	1.81	0.41	0.07	0.01	0.00	0.00	0.00	0.00
11		2	8.19	7.37	3.32	0.99	0.22	0.04	0.01	0.00	0.00	0.00	0.00
12		3	3.00	2.70	1.22	0.36	0.08	0.01	0.00	0.00	0.00	0.00	0.00
13		4	0.83	0.74	0.33	0.10	0.02	0.00	0.00	0.00	0.00	0.00	0.00
14		5	0.18	0.16	0.07	0.02	0.00	0.00	0.00	0.00	0.00	0.00	0.00
15		6	0.03	0.03	0.01	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
16		7	0.01	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
17		8	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
18		9	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
19		10	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Sheet1

Cell Formulas
Range	Formula
C5	C5	=SUM($C$9:$M$19)
C9:M19	C9	=POISSON.DIST(C$8,$C$3,)*POISSON.DIST($B9,$C$2,)*100

Sub Test()

    Dim N As Long, i As Long, hdr() As Long
   
    N = 10
    ReDim hdr(0 To N)
    For i = 0 To N
        hdr(i) = i
    Next i
   
    With Worksheets("Sheet1").Range("C9")
        .Offset(-2).Value = "Away"
        .Offset(-1).Resize(, N + 1).Value = hdr
        .Offset(-1, -1).Value = "Home"
        .Offset(, -1).Resize(N + 1).Value = Application.Transpose(hdr)
        With .Resize(N + 1, N + 1)
            .FormulaR1C1 = "=POISSON.DIST(R" & .Row - 1 & "C,R3C3,)*POISSON.DIST(RC" & .Column - 1 & ",R2C3,)*100"
            .NumberFormat = "0.00"
            .Offset(-4, -1)(1).Value = "Total"
            .Offset(-4)(1).Formula = "=SUM(" & .Address & ")"
        End With
    End With
   
End Sub

I don't, sorry. I'd have to Google it.

The bivariate poisson distribution I came across can only be use on R and I don't know how to use R

 

[Abdmujib]

Hello Stephen, once again thanks for providing the solution to this. I want to ask a question on the next step.

Let take the matrix table as an illustration, let say I want to sum up all the home wins together on the matrix. How can I do that without suming it up manually. Is there a function that will sum up the cells in the selected range when number on the home column is greater than away row. Thanks.

 

[StephenCrump]

Like this perhaps:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Probs
2		Home win	39.91%
3		Draw	30.64%
4		Away win	29.45%
5			100.00%
6
7			Away
8		Home	0	1	2	3	4	5	6	7	8	9	10
9		0	13.53	12.18	5.48	1.64	0.37	0.07	0.01	0.00	0.00	0.00	0.00
10		1	14.89	13.40	6.03	1.81	0.41	0.07	0.01	0.00	0.00	0.00	0.00
11		2	8.19	7.37	3.32	0.99	0.22	0.04	0.01	0.00	0.00	0.00	0.00
12		3	3.00	2.70	1.22	0.36	0.08	0.01	0.00	0.00	0.00	0.00	0.00
13		4	0.83	0.74	0.33	0.10	0.02	0.00	0.00	0.00	0.00	0.00	0.00
14		5	0.18	0.16	0.07	0.02	0.00	0.00	0.00	0.00	0.00	0.00	0.00
15		6	0.03	0.03	0.01	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
16		7	0.01	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
17		8	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
18		9	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
19		10	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
20
Sheet1

Cell Formulas
Range	Formula
C2	C2	=SUM((B9:B19>C8:M8)*C9:M19)/100
C3	C3	=SUM((B9:B19=C8:M8)*C9:M19)/100
C4	C4	=SUM((B9:B19<C8:M8)*C9:M19)/100
C5	C5	=SUM(C2:C4)

 

[Abdmujib]

Yes, thank you so much.

 

[Abdmujib]

It gave me an error (#value) when I tried it. I'm using office 16 could that be the reason?

 

[Abdmujib]

And pls can you help to do for over 2.5 as well. Thanks

 

[Abdmujib]

Thanks I figured it out. Though still not working with my excel 2016. I tried it with office 365 on my phone