Formula Help

[User2000]

Hi,

I need a formula to accomplish the following:

X - Initial amount
Y - Factor

X * Y = A
A * Y = B
B * Y = C
C * Y = D
D * Y = E
F * Y = F
F * Y = G


I need a formula that can sum X+the sum of A-F.

For Example

X = 10,000
Y = .3

10000	0.3	3000
3000	0.3	900
900	0.3	270
270	0.3	81
81	0.3	24.3
24.3	0.3	7.3
7.3	0.3	2.2
2.2	0.3	0.7
	sum=	14285.4

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

I'm looking for a formula that will output 14,285.4. It doesn't have to be 7 iterations, but would like at least 5. Any ideas?

 

[SQUIDD]

Hi

You mean something like this

=G1+(G1*H1)+(G1*H1*H1)+(G1*H1*H1*H1)+(G1*H1*H1*H1*H1)+(G1*H1*H1*H1*H1*H1)+(G1*H1*H1*H1*H1*H1*H1)+(G1*H1*H1*H1*H1*H1*H1*H1)

you enter amount in G1(10000) and factor in H1(0.3) and the formula above in I1 resulting in 14284.78

dave

 

[Eric W]

Depending on how you want to do it, you can build a table with simple formulas you can copy down, or an array formula to do it all in one cell:

	G	H	I	J	K
1	10000	0.3	3000		14285.43
2	3000	0.3	900
3	900	0.3	270
4	270	0.3	81
5	81	0.3	24.3
6	24.3	0.3	7.29
7	7.29	0.3	2.187
8	2.187	0.3	0.6561
9
10
11			14285.43

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula I1 =G1*H1 G2 =I1 H2 =H1 I11 =SUM(I1:I8)+G1 <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula K1 {=SUM(G1*H1^(ROW(INDIRECT("1:9"))-1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you like the table idea, build your table in G1:I8, and put the values in G1:H1. Put the formula in I1 and copy down. Put the formulas in G2:H2 and copy down. Then a simple SUM in I11 gives you the total.

Or you can use the formula in K1. It reads the values in G1:H1 and computes the sum of 9 values (G1 + I1:I8), which is why the formula says 9 instead of 7.

Hope this helps.


Edit: Shoot! My algebra teacher would be so disappointed in me! You just want the sum of a geometric series. There's a simple formula for that, no need for an array formula:

=G1*(H1^9-1)/(H1-1)

 

[BadgerRoad]

This is a geometric series with 7 terms and a common factor of .3. So the formula is 10000*(1-.3^8)/(1-.3)=14284.78.
In general, if you have a common factor of k and n terms the formula will be Initial Amt*(1-k^(n+1))/(1-k)

 

[lrobbo314]

Here's a custom function that should do the trick. It has 3 arguments X, Y, and the number of iterations. So it would look like =Iterations(X, Y, Iterations)

Function Iterations(x As Integer, y As Double, i As Integer) As Double
Dim tot As Double
Dim tmp As Double

tot = x
tmp = tot * y

For j = 1 To i
    tot = tot + tmp
    tmp = tmp * y
Next j

Iterations = tot
End Function

 

[Tetra201]

The same can be achieved using the SERIESSUM function:

=10000*SERIESSUM(0.3,0,1,{1,1,1,1,1,1,1,1,1})

 

[JoeMo]

Your sum is a geometric series multiplied by x. The series for the first n terms (k = 0,1,....,n) has a closed form solution for Abs < 1.

Sum = x*[(1-y^(n+1))/1-y]

Using named ranges: x,y,n:

Excel Workbook
	A	B
1	x	10000
2	y	0.30
3	n	6
4	Sum(6)	14282.59
5	Sum(7)	14284.78
Sheet1