i want to get product of two array (say A(1:10) & B(1:10)) in the
following manner
A1*B10
A2*B9
A3*B8
|
|
|
|
A10*B1

can you please tell me how to do this without using macro? i want to
use only excel functions. i am using MS Excel 2007.

Thanks

Hi

One way, enter in C1
=A1*INDEX(B:B,COUNT(A1:$A$10))
Copy down through C2:C10

--
Regards
Roger Govier

"Rahul" wrote in message
...
i want to get product of two array (say A(1:10) & B(1:10)) in the
following manner
A1*B10
A2*B9
A3*B8
|
|
|
|
A10*B1

can you please tell me how to do this without using macro? i want to
use only excel functions. i am using MS Excel 2007.

Thanks

Hi,

If I've understood correctly then try this

= SUMPRODUCT($A$1:A10,SUBTOTAL(9,
OFFSET($B$1:B10,LARGE(ROW($B$1:B10)-ROW($B$1),ROW(INDIRECT("1:"&ROWS($B$1:B10)))),0,1) ))

Mind the line wrap.

Mike

"Rahul" wrote:

i want to get product of two array (say A(1:10) & B(1:10)) in the
following manner
A1*B10
A2*B9
A3*B8
|
|
|
|
A10*B1

can you please tell me how to do this without using macro? i want to
use only excel functions. i am using MS Excel 2007.

Thanks

one way might be to put in D1

=OFFSET($C$1,-COLUMN()+4+9,-1)

and copy right till M1

then in D3:

=MMULT(A1:A10,D1:M1)

select D3:M12 (or a larger area) and CTRL+SHIFT+ENTER this formula
instead of simply using ENTER

HIH