I'm using Microsoft Excel 2000 and I'm facing a peculiar problem, which I do
not think was in the 1997 version. I'm trying to calculate and build
correlation matrix for an array of data, and I can do it by using the Data
Analysis tool pack. But the excel 2000 version populates only half of the
table (I understand the logic because correlation between two ranges of data
is independent of the order in which the ranges are processed. Cells in the
output table with matching row and column coordinates contain the value 1
because each data set correlates exactly with itself). there use to be an
option, if I remember correctly that one could specify to populate the entire
table (the values just repeat, thus one can thus subject the matrix to
multiplication etc). Can we populate the matrix fully as seen below? my
memory says that there was an option in data analysis tool pack to do that!.
Please help

e.g.
Stock1 Stock2 Stock3
Stock1 1 0.6 0.4
Stock2 0.6 1 0.7
Stock3 0.4 0.7 1

--
lets make money

I see no option in to complete the matrix in Excel XP. Since the matrix
is symmetric, it is straight-forward to complete it yourself.

Jerry

Peppino wrote:

I'm using Microsoft Excel 2000 and I'm facing a peculiar problem, which I do
not think was in the 1997 version. I'm trying to calculate and build
correlation matrix for an array of data, and I can do it by using the Data
Analysis tool pack. But the excel 2000 version populates only half of the
table (I understand the logic because correlation between two ranges of data
is independent of the order in which the ranges are processed. Cells in the
output table with matching row and column coordinates contain the value 1
because each data set correlates exactly with itself). there use to be an
option, if I remember correctly that one could specify to populate the entire
table (the values just repeat, thus one can thus subject the matrix to
multiplication etc). Can we populate the matrix fully as seen below? my
memory says that there was an option in data analysis tool pack to do that!.
Please help

e.g.
Stock1 Stock2 Stock3
Stock1 1 0.6 0.4
Stock2 0.6 1 0.7
Stock3 0.4 0.7 1