Solving a growth equation

  • Thread starter Thread starter Maelstrom
  • Start date Start date
M

Maelstrom

I'm trying to model growth of a simulation that is running. I hav
placed in my spreadsheet, the starting population and the growth rat
per period. After each period the population MUST be rounded off.

Now, I'm trying to solve how long it takes for my population to reach
certain number. Without rounding, this equation is quit
straightforward, however, with rounding I'm not sure if it can b
solved simply with excel.

What' I've done is made a series of cells to show me the populatio
after a certain period, but this becomes burdensome when the initia
variables change.

Is there a function/formula macro that could help me run th
simulations, check to see if the population exceeds the target an
return the period number
 
Maelstrom,

StartValue in A1; GrowthRate (5% for instance) = A2; EndValue in A3
Number of decimals you want to round in A4

=NumberOfTimes(A1,A2,A3,A4) gives you the number for which applies :
Value < EndValue
Number + 1 is the first number for which
Value >= EndValue

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Maelstrom,

Sorry second posting. Forgot to enter the program in the first one.

StartValue in A1; GrowthRate (5% for instance) = A2; EndValue in A3
Number of decimals you want to round in A4

=NumberOfTimes(A1,A2,A3,A4) gives you the number for which applies :
Value < EndValue
Number + 1 is the first number for which
Value >= EndValue


Function NUmberOfTimes(StartValue, GrowthRate, StopValue, Decimals)
Counter = 0
Value = StartValue
While Value < StopValue
Counter = Counter + 1
Value = Round(Value * (1 + GrowthRate), Decimals)
Wend
NUmberOfTimes = Counter
End Function



--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Thank you for the help, just one point of clarification, how do I sav
this new function so I can use it for the spreadsheet
 
Maelstrom,

Press Alt + F11 to invoke the Visual Basic Editor
If necessary start the Process Explorer ( Cntrl + R)
Select the VBA Project with the name of the file you want the macro stored
in.
Open a module (Insert Module from the menu bar)
On the module paste the text from the function.
Go back the "normal" Excel sheet (by clicking the X mark; in the VB editor
this closes the VB editor; Excel stays active)
Save the file.

You now have the function available as long as this workbook is open.
If you want the function always available, store the module in your Personal
macro file which you have if you ever recorded a macro in it.
If you didn't yet, you can do so by recording a macro and choose (from the
drop down box) to record it in your personal macro file.


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top