Array formula for Variance-Covariance matrix?
Jerry W. Lewis
variance covariance of an array? For example, if the data is in A1:C5,
then I want the array formula to return the following 3x3 array
COVAR(A1:A5,A1:A5) COVAR(A1:A5,B1:B5) COVAR(A1:A5,C1:C5)
COVAR(B1:B5,A1:A5) COVAR(B1:B5,B1:B5) COVAR(B1:B5,C1:C5)
COVAR(C1:C5,A1:A5) COVAR(C1:C5,B1:B5) COVAR(C1:C5,C1:C5)
Obviously I could do this manually by entering the appropriate formulas
in each cell, but I am wondering whether it is possible to do in a
single array formula that could itself then be the argument of a matrix
function instead of having to occupy cells in the worksheet.
Jerry
David J. Braden
That's a booger. I don't see how it could be done; why not opt for a
VBA routine to do it? *If* you could get this in a single function
(with the matrix functions; OFFSET won't work here), by the time you
get it in it would be one heck of a long formula, subject to the usual
problems of such.
BTW, did you notice that the COVAR function works over all those ranges
where VARP fails? Weird. Do yo know of any other built-in substitutes
for the VAR and VARP functions?
Regards,
Dave Braden
In article <3BC85DBF.5010008@no_e-mail.com>, Jerry W. Lewis
<post_a_reply@no_e-mail.com> wrote:
--
E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz
Jerry W. Lewis
> BTW, did you notice that the COVAR function works over all those ranges
> where VARP fails? Weird. Do yo know of any other built-in substitutes
> for the VAR and VARP functions?
That was the basis for my first reply in the "LINEST with r2 = -1.18
???" thread from this newsgroup
http://groups.google.com/groups?selm=3BB1DAEC.3080705%40mediaone.net
DEVSQ(), COVAR(), and CORREL() are the only numerically stable Excel
functions that involve second moments of the data. That is why the
above referenced post gave a list of statistical functions to avoid and
higher accuracy workarounds using DEVSQ() COVAR(), & CORREL() instead of
VAR(), STDEV(), regression functions, etc.
Jerry
Harlan Grove
I know you're wary of the standard matrix techniques, but try this
adaptation of the standard matrix covariance formula.
=MMULT(
TRANSPOSE(
A1:E5-MMULT(SIGN(ROW(A1:E5)*COLUMN(A1:E5)),
A1:E5)/ROWS(A1:E5)),
A1:E5-MMULT(SIGN(ROW(A1:E5)*COLUMN(A1:E5)),
A1:E5)/ROWS(A1:E5)
)/ROWS(A1:E5)
This does the right thing by subtracting the column means from each item in
the matrix before performing the sumproducts. Compare it against the COVAR
results - I believe you'll be pleasantly surprised.