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

 

[StephenCrump]

Is there no way to do all the cells automatically at once

Can you please clarify:

- Which cells do you mean? The one matrix, the header numbers 0 to 300? Multiple matrices?
- Do you mean using VBA code?
- Or perhaps do you mean using the newer array formulae (if you have access to the latest versions of Excel, and not just Excel 2016)?

 

[Abdmujib]

I meant instead of using the function to fill ten rows or columns at a time, is it possible to have a function that will do everything once.


Also is it posible to use vba?

Thirdly, pls do you have any material that can guide me on using Bivariate poisson on excel

 

[Fluff]

The earlier post got deleted and I didn't understand the reason

The reason was you bumped your thread after less than 30mins, which takes it of the "Unanswered threads" list thereby reducing you chance of getting help.

Also in future please do not duplicate your threads.

 

[Abdmujib]

Thanks I won't next time.

I got a notification (picture attached) that it was deleted and I didn't understand the reason. I googled the message to see if I could get what its meant, also I search around the pages if I could see FAQ and rules and regulations didn't get any. So I had no option than to post another thread. In fact I included a note in the thread that I didn't k ow why it was deleted.

Since I got notified that it was deleted, why is it still there. It means I'm not the only one to share the fault. The system is at fault too.

Pls kindly explain what you meant by "bump"?

thanks.

 

[Fluff]

So I had no option than to post another thread.

No you did not, your original thread still exists & you should have kept to that, rather than starting a new one.

I got a notification (picture attached) that it was deleted and I didn't understand the reason.

I just explained that in post#13

 

[Abdmujib]

It says it got deleted. Check the screenshot of the notification in the attached pic. It says " Your post in the thread Building Matrix of range of numbers was DELETED. Reason: Please do not bump your thread with 24hrs, as it lessens your chance of getting help"

Delete means only one thing, it does not exist anymore.

I haven't got what you meant by 'bumb', any synonyms?

 

[Fluff]

The message clearly says Your [B]post[/B] in the [B]thread [/B]Building Matrixthat means the post was deleted not the thread, if had followed the link in that notification you would have seen that.

Pls kindly explain what you meant by "bump"?

That is when you reply to your own thread before anybody else has.

 

[Abdmujib]

Oh now I understand. I'm so sorry, thanks for the clarification.

 

[StephenCrump]

I meant instead of using the function to fill ten rows or columns at a time, is it possible to have a function that will do everything once.

Also is it posible to use vba?

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

Thirdly, pls do you have any material that can guide me on using Bivariate poisson on excel

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

 

[Abdmujib]

Thank you so much