Matrix question

[Nightboy]

I want to construct a matrix that will operate as a variance/covariance matrix. There are only 2 possible values for the covariances, based on which sector the stock belongs to. That is, I will have a column that lists 100 stocks & their industry & the same for the top row. If firms are in the same industry, then their covariance is .6 and if they are in different industries the covariance is .4
I'm looking at Excel help re IF & the lookup commands, but I can't figure out how to make this happen.

 

[PaddyD]

With headings in row 1 from b1 across, & in col A from a2 down, put the following in b2 & copy as required:

=IF(OFFSET($A2,-ROW()+1,COLUMN()-1)=$A2,0.6,0.4)

Does it give the desired results?

Book6
	A	B	C	D	E	F	G	H
1		a	b	c	d	a	b	e
2	a	0.6	0.4	0.4	0.4	0.6	0.4	0.4
3	b	0.4	0.6	0.4	0.4	0.4	0.6	0.4
4	d	0.4	0.4	0.4	0.6	0.4	0.4	0.4
5	s	0.4	0.4	0.4	0.4	0.4	0.4	0.4
6	e	0.4	0.4	0.4	0.4	0.4	0.4	0.6
7	a	0.6	0.4	0.4	0.4	0.6	0.4	0.4
8	b	0.4	0.6	0.4	0.4	0.4	0.6	0.4
9	d	0.4	0.4	0.4	0.6	0.4	0.4	0.4
10	e	0.4	0.4	0.4	0.4	0.4	0.4	0.6
Sheet3

Paddy
This message was edited by PaddyD on 2002-09-22 15:09