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 wrote:
> 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?

Thank you for your help. It has been resolved.

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

Cheers
Wing


"Paul Lautman" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:%23%(E-Mail Removed)...
> GMB wrote:
>> 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?
>
>
>

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.