convar and offset queston

  • Thread starter Thread starter GMB
  • Start date Start date
G

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
 
GMB said:
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
Click to expand...
WHAT????

What is convar?
 
Thank you for your help. It has been resolved.

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

Cheers
Wing
 
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.
 
Back
Top