|
Data Generation
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 |
Data Generation
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 |
Data Generation
|
Data Generation
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 |
Data Generation
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 |
Data Generation
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 |
Data Generation
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 |
Data Generation
Hello,
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 http://Reverse("moc.liborplus.www") 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) < "Range" Then GrowthSeries = CVErr(xlErrRef) Exit Function End If If Application.Caller.Rows.Count < 1 And _ Application.Caller.Columns.Count < 1 Then GrowthSeries = CVErr(xlErrValue) Exit Function End If If Abs(dblRate) 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 < -dblMaxRatePerStep Then dblRelMin = -dblMaxRatePerStep End If dblRelMax = (dblCurrMax - dblCurrVal) / dblCurrVal If dblRelMax dblMaxRatePerStep Then dblRelMax = dblMaxRatePerStep End If If dblCurrRate - dblRelMin < 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 |
Data Generation
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 :rolleyes: ) -- rvExcelNewTip ------------------------------------------------------------------------ rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668 View this thread: http://www.excelforum.com/showthread...hreadid=541903 |
Data Generation
Hello,
Your intuition was right. Compare the two versions and you will see my corrections :-) And trust my programs when they reach a version number = V1.00 ... Have fun, Bernd |
| All times are GMT +1. The time now is 02:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com