How can I multiply together every nth cell in a row to get the product?

user_8495863 0
Viewed 293

I want to calculate the product of the investment returns (compound interest), which in this instance would give a return of 162%:

enter image description here

Additional stock return data will be added in future, so I need a formula that compounds every nth (2nd) value. I am able to sum the cells using: =SUMIF(ARRAYFORMULA(MOD((COLUMN(A2:H2)-COLUMN(A2)+1),2)),0,A2:H2)

However I cannot find an equivalent 'PRODUCTIF' function and have not been able to make a formula that works. Writing the formula out long hand would be: =B2*D2*F2*H2

google-sheets
3 Answers

Product formula should be like Sum formula but putting 1 for odd columns:

=ArrayFormula(PRODUCT(IF(MOD(COLUMN(A2:H2)-COLUMN(A2)+1,2),1,A2:H2)))

enter image description here

user_38949170

I guess i'm a little confused. doesn't PRODUCT() just ignore text?

Doesn't =PRODUCT(2:2) work?

user_110751290

You can also use the following formula: 

=PRODUCT(TRANSPOSE(2:2))

and format the result as Percent from the menu

enter image description here

Functions used:

EDIT
As correctly noted by Mat, an even simpler formula can also give the correct results. 

=PRODUCT(2:2)
user_15277800
Related