Need help with formula on Excel

UPDATE 1:

This is what I am trying to do.

B1, B2, and B3 represent 3 containers

Period could be anything, days, weeks, months years, so Period 1, can mean day 1, or week 1, or month 1 or year one.

In period 1, I start off with the value 100. 60% of 100 always gets added to B1 (container 1), and 40% of 100 always gets added to B2 (container 2). B3 (container 3 does not get a percentage of 100. This happens on every period.

After the first period, i.e. period 2, B1, B2, and B3 all get 10% of the value of the previous B2 values divided by 3. So each container gets 1.33 in this example. However, B1 also gets another 60% of 100 added to it making the total 61.33 and B2 gets another 40% of 100 added to it making the total 41.33.

In the next period, period 3, again 10% of the values from B2 above period 3 divided by 3, i.e. ((40+41.33)*0.10)/3 gets added to all cells in period 3, then for B1, add another 60% of 100, and B2 get another 40% of 100.

ORIGINAL QUESITON:

I have the following spreadsheet which uses some basic formulas.

https://spreadsheets.google.com/spreadsheet/ccc?key=0Al-CN3-vjUYvdEdNMU4zN3FTNF9VZlMzZXBTcjh0Mnc&hl=en_US#gid=0

As you can see this goes from period 1 to 12. Is it possible to create a formula for columns B1, B2, and B3 to get the values based on the period? So, for example, if the formula was working, I would be able to simply type in 5 to get:

B1 = 65.61
B2 = 45.61
B3 = 5.61
Used = 500.00

If I type in 100 as the period, it should开发者_StackOverflow社区 return the correct values, instead of creating 100 rows with the appropriate formulas per cell, which would take ages to do.

Does anyone know how to create the required formulas?

---

As shown online in your spreadsheet, you can use SUMIF:

For B1:

=SUMIF(C2:C13;A4;D2:D13)

And so on for B2, B3 and Used.

EDIT

I solved your issue on another Sheet of your Google Doc.
Needed some maths to found out which kind of sequence this was in order to sum up each element to the n-th one.

This was done for column E (meanly title B2):

=$A$2*$I$8*POWER($I$2/3+1;C2-1)

And then adapted to fit B1, B3 and Used, wasn't that easy...

---

For Ease Why dont you convert

=(((E2+E3+E4+E5+E6+E7+E8+E9+E10+E11+E12)*I2)/3)+(A2*I5)

to

=((SUM(E2:E12)*I2)/3)+(A2*I5)

---

Before all this math stuff isn't my best Art, but I figure out the answer anyways

Well, even when you can macro your function, some understanding of the formulae can help to a simpler answer: First, the key column is B, if you analize his result you will identify this as a ''series'' (the math stuff, link to wikipedia here), checking of the results are calculated yield the followin table:

please, don't force to explain, because isn't my best area of expertise, but this is the solution (use a scientific calculator or mathematica to check this out), using this formulae you getthe value of the B column.

So, Column A and C, how we get it? A quick inspection show the following: A=B+20 C=B-40

I hope this solves your question Best Regards Alen