convar and offset queston

[GMB]

Hi all

I try to do

convar(A1:$A$10,$B$1:B10)
convar(A2:$A$10,$B$1:B9)
convar(A3:$A$10,$B$1:B8)
convar(A4:$A$10,$B$1:B7)
.....
convar(A10:$A$10,$B$1:B1)

Getting the first argument (A1:A10) -> (A10:A10) with problems

but how to get (B1:B10) -> (B1:B1)


I tried to use Offset to decrement the ending cell without any success.

Some knows how to do it?

Cheers
Wing

 

[Paul Lautman]

Hi all

I try to do

convar(A1:$A$10,$B$1:B10)
convar(A2:$A$10,$B$1:B9)
convar(A3:$A$10,$B$1:B8)
convar(A4:$A$10,$B$1:B7)
....
convar(A10:$A$10,$B$1:B1)

Getting the first argument (A1:A10) -> (A10:A10) with problems

but how to get (B1:B10) -> (B1:B1)


I tried to use Offset to decrement the ending cell without any
success.
Some knows how to do it?

Cheers
Wing

WHAT????

What is convar?

 

[GMB]

Thank you for your help. It has been resolved.

It is my typo, should be covar(array1,array2).

Cheers
Wing

 

[aresen]

Usually, I can do something like =SUM(INDIRECT("B1:B"&11-ROW()) and get
what you're looking for. This example requires the first cell in the
range be in row 1 (11-ROW()) would start you off at 10 and decrement as
you copy it downward. The 11 can be adjusted depending on the row of
the first formula.
I said usually. If you are using the covariance function, Excel will
balk at this. Instead, you need to put the ="B1:B"&11-ROW() in a cell
(say C1) then =COVAR(A1:A$10,INDIRECT(C1)) in another cell. Then copy
them down over the 10 row range. This is the simplest way I can think
of but it takes two columns.