You can select the '100' thru the '110' cells, go to
menu>Edit>Fill>Series and select from there.
jeff
-----Original Message-----
Hello all,
I posted a question a few days ago and got an answer,
but now i would like to develop the formula a bit further.
HERE IS MY FIRST QUESTION:
newbie question..
I have 5 cells (A2:E2) which have different percentages
the headers of these cells (A1:E1) have year-end date
eg. 12/31/2004, 12/31/2005, etc
then i have another row (A3:BC3) with month-end dates
starting with 6/30/2004 through 12/31/2008
beneath those i have a june start value of 100 in cell A4..
is there a formula that will apply the growth rates from
A2:E2 to the starting value starting in december 2004
and "carry" that value across until we reach another
december, and then apply the corresponding growth rate to
it?
EXAMPLE: assuming we have 10% in all cells A2:E3
Jun-04 = 100
Jul-04 = 100
Aug-04 = 100
Sep-04 = 100
Oct-04 = 100
Nov-04 = 100
Dec-04 = 110
Jan-05 = 110
Feb-05 = 110
Mar-05 = 110
Apr-05 = 110
" "
" "
" "
Dec-05 = 121
Jan-06 = 121
Feb-06 = 121
etc etc etc
Thank you
HERE IS THE ANSWER GIVEN:
Hi!
I would start by making some simple changes/ additions to you data
structure.
You have your various percentage growth factors in A1 to E1.
Create a lookup table somewhere out of the way - let's suppose it is
located at CA1:CE2. Give it a name (Insert> Name> Define). I'll call it
LUT (for lookup table: no imagination).
In CA1 put 2004 (the year) and so on across the row.
Next the messy bit: the area of "compound interest".
In CA2 put =A1.
In CB2 put =(1+A1)*(1+B1)-1
In CC2 put =(1+A1)*(1+B1)*(1+C1)-1
You'll get the drift for CD2and CE2
Your month end and year end rows don't need most of their elements.
Life would be easier if you could separate them into Month (Jan, Feb
etc) in one row (say row 3) and Year (2004 etc) in the next (say row
4). This would then give us a quantity (Year) to access the lookup
table and select the appropriate growth factor.
Now for the formula, which is quite straightforward:
Assuming A5 contains your starting figure 100, put in B5
=$A$5*(1+HLOOKUP(B4,LUT,2,FALSE))
Now copy this across the piece to 2008.
Alf
HERE IS MY FOLLOW-UP QUESTION:
Thank you for that great formula. I made the changes suggested and it works.
I made one small change. Since I need the growth rate to
be applied ONLY in December of the year, I changed the
headers in CA1:CE1 to be year-end dates and also kept my
original headers (with month-end dates) across row 3. Now
the formula only applies the growth rate in December and
also does the compounding (a step that I did not account
for, thanks!)
The only problem I have now is with the months that are
not december. I am getting #N/A's. Is there a way to have
those months gradually work there way up to the december
value?
EXAMPLE:
Let's say we are starting with December 2004, and we
manually input that starting value of 100.
