Thread: Automate formula "=(1+A1)*(1+A2)*(1+A3)....-1" to add each row in a range of cells

Trying to automate the formula "=(1+A1)*(1+A2)*(1+A3)....-1"
Want to replace A1, A2, A3 etc with a self find function in excel 2003 without creating a macro.

Test Data:
Cell A1 = 1%
Cell A2 = 3%
Cell A3 = 5%
Cell A4 = 7%
Cell A5 = 9%
Cell A6 = -2%
Cell A7 = -4%
Cell A8 = -6%
Cell A9 = -8%
Cell A10= -10%
Cell A11= 0.5%

Manual Solution: A1->A10 =(1+A1)*(1+A2)*(1+A3)*(1+A4)*(1+A5)*(1+A6)*(1+A7)*(1+A8)*(1+A9)*(1+A10)-1 = -6.71%
Manual Solution: A1->A11 =(1+A1)*(1+A2)*(1+A3)*(1+A4)*(1+A5)*(1+A6)*(1+A7)*(1+A8)*(1+A9)*(1+A10)*(1+A11)-1 = -6.25%

Automated Solution: A1->A10 =???? = -6.71%
Automated Solution: A1->A11 =???? = -6.25%


Conditions:
1. every row must be added like the above formula between a range (for example cell A1 thru cell A10)
2. When a new row of data is added to the bottom (cell A11) the formula should automatically adjust without having to manually add (1+A11) to the formula. There are chances that 100's of rows will be added at a single time so manually adding (1+A12)*(1+A13) will be too time consuming.

The (1+x)*(1+x)-1 formula gets me the total % return for multiple periods. AVG does not work nor does a SUM feature.
Is it possible to adobt a range function?

Any ideas ? Really appreciate your help.
I've already done a google search but nothing specific to this formula.

Thanks!
Brett

That worked perfectly! Thanks!
You just saved me hours and hours of work.

You mind explaining what the Ctrl+Shift+Enter does differently than just hitting Enter? For my knowledge.