How to multiply a range of values without creating a new column

[BNCOXUK]

Hi, I have a column called A1: A1000, whose values are the monthly return of a stock. My task is to get the value of: (1+A1)*(1+A2)*(1+A3)* ... * (1+A1000).

It is easy if I create a new column equal to 1+A1, 1+A2, ... 1+A1000, and then use the product() function. But is there a clever way that I don't need to create a new column and can also get the (1+A1)*(1+A2)*(1+A3)* ... * (1+A1000) value? This is really helpful if no more columns need to be added.

Many thanks for your advice.

[Fotis1991]

Hi

Maybe

=SUM(A:A)+COUNTA(A:A)


Edit: OR do you mean?

=SUM(A:A)*COUNTA(A:A)

[Ace_XL]

How about an array formula..

=PRODUCT(1+A1:A1000)

Confirm with Ctrl+Shift+Enter not just Enter

[BNCOXUK]

How about an array formula..

=PRODUCT(1+A1:A1000)

Confirm with Ctrl+Shift+Enter not just Enter

This is a super answer. Job done! thanks a lot

[Marcol]

If you wrap PRODUCT() in SUMPRODUCT() you avoid having to use Ctrl+Shift+Enter.

Please Login or Register  to view this content.