Multiplying and adding a sequence

[Bananas212]

Hello all, I'm having a bit of a problem with a formula. Attached are the values, and what I'm trying to get is a total product of (a2+a2*a3+a2*a3*a4+a2*a3*a4*a5....etc, on down to the last value). I've tried multiple formulas with rows and sumproduct, etc., but nothing seems to be working! Thanks for looking!

~Laura

[Tony Valko]

Assuming this is you data:

Data Range

	A
1	x
2	0.95098
3	0.94845
4	0.94565
5	0.94253
6	0.93902
7	0.93506
8	0.93056
9	0.92537
10	0.91935

Tell us what result you expect.

[Bananas212]

I would expect to get
(a2)+
(a2*a3)+
(a2*a3*a4)+
(a2*a3*a4*a5)+
.
.
.
.
on down to
+(a2*a3*a4*a5*a6*a7*a8*a9*a10)= one product which I would like to display in one cell
I don't know if there's a formula to do this, but if so it would save me hours. I'm a bit of a beginner, so any help would be appreciated!

[shg]

In B2 and copy down,

=A2*(1+B3)

	A	B
1	x
2	0.95098	10.38467
3	0.94845	9.91997
4	0.94565	9.45914
5	0.94253	9.00279
6	0.93902	8.55173
7	0.93506	8.10708
8	0.93056	7.67012
9	0.92537	7.24248
10	0.91935	6.82657
11	0.91228	6.42543
12	0.90385	6.04327
13	0.89761	5.68614
14	0.89185	5.33476

[Tony Valko]

In B2 and copy down,

=A2*(1+B3)

Nice one!

I'm not having much success getting it all in a single formula.

[Bananas212]

Hi! So that works perfectly for giving me the correct value in the top cell (b2), but the problem is that I only want that value in b2 to show up without having anything below that. But when I don't drag the formula down to b14, it doesn't give me the correct answer. Any other ideas?

[Bananas212]

So I figured out a "cheating" solution... I went under format cells and hid the values under b2 from being able to be seen. This works well enough for me, but if you do come up with some sort of solution let me know! Much appreciated guys! Thanks again (:

[shg]

Single-cell formula:

	A	B	C	D	E
1	x	Old		New
2	0.95098	10.38467	B2: =A2*(1+B3)	10.38467	D2: {=SUM(SUBTOTAL(6, OFFSET(A2, 0, 0, ROW(INDIRECT("1:" & ROWS(A2:A98))))))}
3	0.94845	9.919972
4	0.94565	9.459141
5	0.94253	9.002792
6	0.93902	8.55173
7	0.93506	8.10708
8	0.93056	7.670118
9	0.92537	7.242475
10	0.91935	6.826572
11	0.91228	6.425433

[Bananas212]

Thanks for replying. For some reason that previous formula didn't work either. Maybe I have some sort of setting turned on that shouldn't be?

[shg]

The curly braces mean that you need to confirm the formula with Ctrl+Shift+Enter instead of just Enter; you can't type the braces in manually.

[Bananas212]

Perfect! Thank you so much! I believe this is the second time you've helped me tremendously! You're very good. Do you have any tutorials or books on excel that you've used & could recommend to me? I'm pretty much a beginner and would like to some day get to your level of excel knowledge.

[shg]

Hiker updates a decent list of resources periodically: http://www.mrexcel.com/forum/excel-q...ml#post3755647

[Bananas212]

Thank you!


Sent from my iPhone using Tapatalk

[shg]

You're welcome.