GROWTH formula

[brett.kaplan]

Hi,

I am trying to create a formula that I can use that will mimic using
the Edit -> Fill -> Series -> Trend & Growth values.

Is there a way to do this? Basically, I know my starting X is 10 and
my starting Y is 16.53509 and my ending X is 30 and my ending Y is 5,
I would like Y values for 11-29.

So in column A I have my X's (10-30) and in column B I have my Y's
(16.53509 {blanks] 5).

Any help is appreciated!

Thanks!

 

[Gary''s Student]

In B1 enter 16.53509
In B2 enter =B2-(16.53509-5)/20 and copy down.

In A1 thru B20 we see:

10 16.53509
11 15.9583355
12 15.381581
13 14.8048265
14 14.228072
15 13.6513175
16 13.074563
17 12.4978085
18 11.921054
19 11.3442995
20 10.767545
21 10.1907905
22 9.614036
23 9.0372815
24 8.460527
25 7.8837725
26 7.307018
27 6.7302635
28 6.153509
29 5.5767545
30 5

 

[brett.kaplan]

Hi,

That will only give me a linear trend. The growth does it
exponentially, so the values are different.

 

[brett.kaplan]

As a followup to this, I can do it if I make a 4 box range with 10 &
30 in one column and 16.53509 & 5 adjacent to those. However, I'd
like to be able to do this in a single cell. Is there a way to
reference an array like that using values instead cell references?

 

[Gary''s Student]

You are correct. In C1 enter: 0.94195069
and in B2 enter =B1*$C$1

Now we see:

10 16.53509
11 15.57523943
12 14.67110752
13 13.81945985
14 13.01724974
15 12.26160737
16 11.54982952
17 10.87936988
18 10.24782996
19 9.652950504
20 9.092603385
21 8.56478403
22 8.067604225
23 7.599285364
24 7.15815209
25 6.742626299
26 6.351221493
27 5.982537466
28 5.635255292
29 5.308132609
30 4.999999173

Now the growth is not linear. It is more like compound interest.

By the way, the value in C1 is not a "magic" number. It is derived either
by using the standard exponential formula or by using Solver.

 

[brett.kaplan]

Aside from using solver, because I don't want to use an extra cell for
the "magic number" in C1, what is the formula I can use to get there?

Thanks a lot for the help!

 

[David Biddulph]

How much are you being charged for using an extra cell?

But we'll undercut that charge and do it by saying that C1
=(B21/B1)^(1/(A21-A1))

 

[brett.kaplan]

Thanks so much!