This is probabably a mathematical rather than an excel question, but I
give it a try. I want to generate a "random" data series with the
following characteristics:
1. The overal series should have a X% compounded growth rate
2. Each point should be within a Y% interval around the previous
point.
(Y X)

For instance, the data series should show a 4% compounded growth rate,
but from one point to the next there can be a variation of +/- 12%.

Has somebody a hint as to how to construct such a function?


--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
View this thread: http://www.excelforum.com/showthread...hreadid=541903

An interesting problem and easy to model in Excel:

In A1 put a starting value, say 100. In B1 we put a random sample from 12%
below A1 to 12% above A1 (that is from 88 to 112). The equation for this is:

=RANDBETWEEN(A1*0.88,A1*1.12)

Row 2 is the next year and in A2 we put:
=B1*1.04
Which is the 4% growth over the previous year's randon sample. A2 is the
new center point, so in B2:

=RANDBETWEEN(A2*0.88,A2*1.12)


We now propagate row 2 down the columns:
Select A2 and B2 and copu them down as far as you like.
--
Gary's Student


"rvExcelNewTip" wrote:


This is probabably a mathematical rather than an excel question, but I
give it a try. I want to generate a "random" data series with the
following characteristics:
1. The overal series should have a X% compounded growth rate
2. Each point should be within a Y% interval around the previous
point.
(Y X)

For instance, the data series should show a 4% compounded growth rate,
but from one point to the next there can be a variation of +/- 12%.

Has somebody a hint as to how to construct such a function?


--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
View this thread: http://www.excelforum.com/showthread...hreadid=541903

Hello,

I suggest to use:
http://www.sulprobil.com/html/growthseries.html

HTH,
Bernd

I used the RandBetween function as suggested by Garry's Student. The
approach seems logical, but I'm not certain it does meet the criteria.
I generated 20 times the series for 500 integer data points (from A1 to
A501). I used each time a different starting point: A1 varied from 10
to 200 with steps of 10. I then computed the resulting growth rate as
follows:

-=ROUND(100*((A501/A1)^(1/500)-1),2).-

The resulting growth rate was 19 times out of 20 less than 4 (average
3.62 minimum 2.82 and once 4.32). I know that working with random
numbers can be tricky, but nevertheless I expected a more evenly
distributed growth rate around an average of 4.

Is the sample to small or is somehow the approach flawed?

I also tested the GrowthSeries function and it showed a very
interesting behaviour. It's an array formula and I filled a range of
500 cells with it.

-=GrowthSeries(0.04,0.12,10)-

Again I varied the starting point from 10 to 200 and I calculated the
growth rate. The good news is that the average over the 20 samples
calculated by the same formula as above, was ... 4.00. But the series
showed a strange behaviour. It grew more rapidly than the first (which
it should), but after some 450 data points it started to decline very
quickly to attain the "correct" growth rate at the end point.

When I reduced the sample to 450 cells, then the average growth rate
was ... 4.01. But again it surged for about 410 cells and then again it
declined to the correct value! A closer inspection revealed that the
function algorithm does use the cell count to determine some bounds and
probably this skews the results to the target value near the end.

I'm afraid that both suggestions have their problems?


--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
View this thread: http://www.excelforum.com/showthread...hreadid=541903

Hello,

I get at the end 9247677589.34 which is precisely 4.00% as expected:
=200*(1+4%)^450

My algorithm starts to generate random numbers with the full given
flexibility of +/-12% per step - until it reaches boundary conditions
to meet the final preset result.

There are zillion ways to come from 200 to 9247677589.34 in 450 steps
:-) And each of these are "skewed" by the condition "result =
9247677589.34". Now you can decide which "skew" you would fancy.

Would you like the random numbers to be generated with a flexibility
between -4% and +12% (meaning: on average equal to the given compound
growth rate)?

Regards,
Bernd

Bernd,

It is just that I expected over 500 data points more fluctuations
around an average growth rate. But the generated values grow steadily
to a maximum (at around 450 at which point the compounded rate was
about 5.8) and then they fell steadily to the 4.0 growth rate at the
end. I expected for random points a more "sawtooth" like pattern with
some more local max-and minima.

The same pattern existed when I limited the data range to 450 data
points: a steady rise for about 410 points and then decline - just
once.

I then extended the series to 1000 data points - same pattern: rises
for about 900 points, then fall. Could it be a chance hit that in each
case the series rises for about 90% of the time?

But then again: the algorithm seems correct, but in practice it is not
entirely satisfactory. A trick played by the random devil?


--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
View this thread: http://www.excelforum.com/showthread...hreadid=541903

As an afterthougth, I think that the basic question is:

Can I generate a data series such that the values are "randomly" chosen
from the interval [-12,+12] but such that their average is 4? (Which
means that the series is not totally random!)

If I can generate such a series, I can then use its values as succesive
multipliers to generate the growth series.


--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
View this thread: http://www.excelforum.com/showthread...hreadid=541903