Re: 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?action=getinfo&userid=15668
View this thread: http://www.excelforum.com/showthread.php?threadid=541903

.

Relevant Pages

  • Re: Excel Formula / Ignore Blank Cells?
    ... > I'm working on a financial formula, trying to calculate a growth rate ... > for a row of cells. ... > spreadsheet grows larger, or the data sets change over time, it means I ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Excel formula / How to ignore blank cells?
    ... it's more of a smoothed average calculation. ... Right now I have a row of numbers across cells, ... In L2 I want to determine the growth rate for this period, ... formula to ignore them and adjust the forumula accordingly. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: cant figure it
    ... Excellent Govind, thanks very much ... > Lets assume that the user matrix is in the range of cells A1 to B5, ... >> I want to set a default annual growth rate for each of 40 years and then let ... >> the second column the amended growth rate. ...
    (microsoft.public.excel.worksheet.functions)
  • help with =$A$5*(1+HLOOKUP(B4,LUT,2,FALSE))
    ... You can select the '100' thru the '110' cells, ... and then apply the corresponding growth rate to ... >LUT (for lookup table: no imagination). ... original headers across row 3. ...
    (microsoft.public.excel.misc)