software

This is probabably a mathematical rather than an excel question, but I
give it a try. I want to generate a quot;randomquot; 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 gt; 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: www.excelforum.com/member.php...oamp;userid=15668
View this thread: www.excelforum.com/showthread...hreadid=541903An 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 Studentquot;rvExcelNewTipquot; wrote:

gt;
gt; This is probabably a mathematical rather than an excel question, but I
gt; give it a try. I want to generate a quot;randomquot; data series with the
gt; following characteristics:
gt; 1. The overal series should have a X% compounded growth rate
gt; 2. Each point should be within a Y% interval around the previous
gt; point.
gt; (Y gt; X)
gt;
gt; For instance, the data series should show a 4% compounded growth rate,
gt; but from one point to the next there can be a variation of /- 12%.
gt;
gt; Has somebody a hint as to how to construct such a function?
gt;
gt;
gt; --
gt; rvExcelNewTip
gt; ------------------------------------------------------------------------
gt; rvExcelNewTip's Profile: www.excelforum.com/member.php...oamp;userid=15668
gt; View this thread: www.excelforum.com/showthread...hreadid=541903
gt;
gt;

Hello,

I suggest to use:
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 quot;correctquot; 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: www.excelforum.com/member.php...oamp;userid=15668
View this thread: www.excelforum.com/showthread...hreadid=541903Hello,

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 quot;skewedquot; by the condition quot;result =
9247677589.34quot;. Now you can decide which quot;skewquot; 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 quot;sawtoothquot; 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: www.excelforum.com/member.php...oamp;userid=15668
View this thread: 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 quot;randomlyquot; 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: www.excelforum.com/member.php...oamp;userid=15668
View this thread: www.excelforum.com/showthread...hreadid=541903Hello,

I think that the randomness has to be skewed in that way that the
algorithm should recallibrate the middle point of the random interval
to the current necessary compounded growth rate (which is detemined by
current value, original ending value and count of remaining steps),
meaning:

Function GrowthSeries(dblRate As Double, dblMaxRatePerStep As Double, _
Optional dblStartVal As Double = 1#) As Variant
'Algorithm by: sulprobil Reverse(quot;moc.liborplus.wwwquot;) V0.9
'Returns random data with a compounded growth rate dblRate, with
'a maximal relative change rate per step of dblMaxRatePerStep and
'with a start value of dblStartVal.
'The number of periods is implicitly chosen by the number of selected
'cells which call this function as an array formula (entered with
'CTRL SHIFT ENTER).

Dim vR As Variant
Dim lP As Long 'Periods
Dim lrow As Long
Dim lcol As Long
Dim dblCurrVal As Double
Dim dblCurrRate As Double
Dim dblCurrMin As Double
Dim dblCurrMax As Double
Dim dblRelMin As Double
Dim dblRelMax As Double
Dim dblEndVal As Double

Application.Volatile

If TypeName(Application.Caller) lt;gt; quot;Rangequot; Then
GrowthSeries = CVErr(xlErrRef)
Exit Function
End If

If Application.Caller.Rows.Count lt;gt; 1 And _
Application.Caller.Columns.Count lt;gt; 1 Then
GrowthSeries = CVErr(xlErrValue)
Exit Function
End If

If Abs(dblRate) gt; dblMaxRatePerStep Then
GrowthSeries = CVErr(xlErrNum)
Exit Function
End If

lP = Application.Caller.Count

ReDim vR(1 To Application.Caller.Rows.Count, _
1 To Application.Caller.Columns.Count)

dblCurrVal = dblStartVal
dblEndVal = dblStartVal * (1# dblRate) ^ CDbl(lP)
dblCurrMin = dblEndVal / (1# dblMaxRatePerStep) ^ CDbl(lP)
dblCurrMax = dblEndVal / (1# - dblMaxRatePerStep) ^ CDbl(lP)
For lrow = 1 To UBound(vR, 1)
For lcol = 1 To UBound(vR, 2)
dblCurrRate = (dblEndVal / dblCurrVal) ^ _
(1# / CDbl(lP - lcol * lrow 1)) - 1#
dblCurrMin = dblCurrMin * (1# dblMaxRatePerStep)
dblCurrMax = dblCurrMax * (1# - dblMaxRatePerStep)
dblRelMin = (dblCurrMin - dblCurrVal) / dblCurrVal
If dblRelMin lt; -dblMaxRatePerStep Then
dblRelMin = -dblMaxRatePerStep
End If
dblRelMax = (dblCurrMax - dblCurrVal) / dblCurrVal
If dblRelMax gt; dblMaxRatePerStep Then
dblRelMax = dblMaxRatePerStep
End If
If dblCurrRate - dblRelMin lt; dblRelMax - dblCurrRate Then
dblRelMax = 2# * dblCurrRate - dblRelMin
Else
dblRelMin = 2# * dblCurrRate - dblRelMax
End If
dblCurrVal = dblCurrVal * (1# (dblRelMin dblRelMax) / _
2# (Rnd() - 0.5) * (dblRelMax - dblRelMin))
vR(lrow, lcol) = dblCurrVal
Next lcol
Next lrow

GrowthSeries = vR

End Function

HTH,
Bernd
Bernd, thank you for your persistence. I tested the function for
different starting values and different numbers of data points and each
time a sawtooth pattern resulted with more up than downs. This is to be
expected as the overall growth is positive. The new function shows the
pattern that I intuitively expected (intuition is mostly wrong in
randomness )--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: www.excelforum.com/member.php...oamp;userid=15668
View this thread: www.excelforum.com/showthread...hreadid=541903Hello,

Your intuition was right. Compare the two versions and you will see my
corrections :-) And trust my programs when they reach a version number
gt;= V1.00 ...

Have fun,
Bernd