exponential curve / growth formula

[rogerthat]

Please help! This one's a thumper and everyone (all my excel 'genius' friends) are stumped. And we may be doing this wrong but if anyone can shed light, would be extremely grateful.

We are given a few numbers to start with:

100,000 This is our "overall" goal (number of calls to be made).
20 This is the number of "week" we have to reach our overall goal.
500 This is the goal for Week 1

With those numbers, we need to come up with weekly goals for 20 weeks that total 100,000 (for example):

Week, Goal
1, 500
2, 603
3, 727 ... and so forth

The trick is that these goals "cannot" be created on a linear path but rather need to be created on a exponential curving growth path. In other words, the goals will start low and over time create a curving increase.

Step I would assume is figuring out the goals with only 100,000 total, 500 start, and 20 weeks. This is where we got lost. So I worked backward to try and see how is "should" look.

So I hard coded a percentage of 20.5682366195176% for each week and this number works perfect when applied to the next weeks goal. I.E. It is this 20.5682366195176% that we're trying to find a formula for given ONLY the numbers we have from the beginning.

For example, see list below:

500 x 20.5682366195176% = 603
603 minus 500 = 103
103 divided by 500 = 20.5682366195176%

If you plot the "goal" numbers on a graph, you get a nice exponential growth curve. As well as, if you add ALL the goals up, you get 100,000. So finding the 20.5682366195176% is the key. But is it possible???


Week, Goal, Growth, %
1, 500,
2, 603, 103, 20.5682366195176%
3, 727, 124, 20.5682366195176%
4, 876, 149, 20.5682366195176%
5, 1,057, 180, 20.5682366195176%
6, 1,274, 217, 20.5682366195176%
7, 1,536, 262, 20.5682366195176%
8, 1,852, 316, 20.5682366195176%
9, 2,233, 381, 20.5682366195176%
10, 2,692, 459, 20.5682366195176%
11, 3,246, 554, 20.5682366195176%
12, 3,913, 668, 20.5682366195176%
13, 4,718, 805, 20.5682366195176%
14, 5,689, 970, 20.5682366195176%
15, 6,859, 1,170, 20.5682366195176%
16, 8,269, 1,411, 20.5682366195176%
17, 9,970, 1,701, 20.5682366195176%
18, 12,021, 2,051, 20.5682366195176%
19, 14,493, 2,472, 20.5682366195176%
20, 17,474, 2,981, 20.5682366195176%

So if we can figure out the formula to get 20.5682366195176%, we can then apply that to create the weekly goals and thus also have our curving growth all in one.

Thanks for any help out there..

 

[shg]

What's wrong with GolaSeek or Solver? It may not be as pretty as a closed-form solution, but it certainly gives the answer.

 

[rogerthat]

short answer is we're going to be doing alot of these and a formula that figures it out for us automatically really makes our life easy.

long answer, only because we're desperate and (because i don't know how to attach the file i'm working on) if you want to setup your excel doc with exactly what we're looking at. Will tell you exactly what we have in each cell, by cell word for word (some cells are headers like A1-A6), some are numbers with no formulas (B1-B6) and (D1-W1), and others are formuals (easy drag)

A1- Approx Scripts/Hr
A2- Hours Dialing/Week
A3- Number of Weeks
A4- Starting Week Dials
A5- Total Calls
A6- %Growth/Week

B1- 40
B2- 60
B3- 20
B4- 500
B5- 100,000
B6- 20.6% or 20.5682366195176%

D1 thru W1 = numbers 1-20, so D1=1, E1=2, F1=3, G1=4 etc.. This is the weeks.
D2 =B4 (formula)

E2 =IF(E1<=$B3,ROUND(D2*(1+$B6),0),0)
Drag E2 formula across to the right to cell W2

E3 =IF(E1<=$B3,E2-D2,0)
Drag E3 formula across to the right to cell W3

E4 =IF(E1<=$B3,E3/D2,0)
Drag E4 formula across to the right to cell W4

Finally, in Cell X2, put =SUM(D2:W2)

So there you have it.. 20 weeks from Column D - Column W


* What you'll notice now is that we have to tweak cell B6 in order to figure out what percent gets us to the total sum in cell X2. Remember, X2 needs to = B5. i.e. 100,000 is our goal. So when i told you earlier, about 20.5682366195176%, i had to figure that number out and even still, i don't think it adds up to 100,000 perfectly. for kicks, change B6 to 10% or 15% and look at cell X2. you'll see now that you have to tweak B6 up to get close to 100,000.

Point is, we don't want to have to figure out that 20.5682366195176% number every time. there should be a formula that can do this for us because we have alot of these to do. Heck, we know that number 20.5682366195176% works because it's being reflected in the spreadsheet right now. We just need to work it backwards and figure that number out with a formula.

Final thought is, this is what we are currently using and looking at. and we are manually figuring out the value for cell B6. But is there an easier way? (without using goal seek), but rather putting a formula in B6 that figures out what the %Growth/Week "should be".

If you've made it this far, you are a champions in my book and I'm glad i joined this excel community. Thanks much.

 

[circledchicken]

Hi,

I might be wrong, but I think you are still not using Goal Seek correctly as it does exactly what you describe and very quickly.

One possibility given your original spreadsheet set-up is that Goal Seek would fail to find a solution because your looking for an exact 100,000 using whole numbers (rounded from their exact values).

Consider a slightly revised version of your spreadsheet below. Drag all the formulas in column E across to column W. Note that cell X2 is not required and there is now a new row with rounded numbers that should sum exactly to your desired value each time Goal Seek is run (row 3). Also ensure W3 has the correct formula as it should be different to the rest of the row 3 formulas.

Excel Workbook
	A	B	C	D	E	W
1	Approx Scripts/Hr	40		1	2	20
2	Hours Dialing/Week	60		500	505	604.0545
3	Number of Weeks	20		500	505	606
4	Starting Week Dials	500			5	7.501997
5	Total Calls	11,010			1.000%	1.255%
6	%Growth/Week	1.000%
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B5	=SUM(D2:W2)
D2	=B4
D3	=D2
E2	=D2*(1+$B$6)
E3	=ROUND(E2,0)
E4	=E3-D3
E5	=E4/D3
W2	=V2*(1+$B$6)
W3	=B5-SUM(D3:V3)
W4	=W3-V3
W5	=W4/V3

Now, with the spreadsheet set up exactly as above, follow the steps below:

• Click on cell B5
• Select Goal Seek
• 'Set cell' should be B5
• Change 'To value' to 100,000
• Change 'By changing cell' to B6
• Click OK

If you have a lot of these you need to do then you can use Solver which can save the results and give you a nice summary.

If there are hundreds or thousands of these you need to figure out then a small amount of VBA code could automate this for you. For example consider the following code:

Sub GrowthRates()

Dim c As Range

For Each c In Range(Range("A9"), Range("A9").End(xlDown))
    Range("B5").GoalSeek Goal:=c, ChangingCell:=Range("B6")
    c.Offset(0, 1) = Range("B6")
Next c

End Sub

And here are the results for different example target values:

Excel Workbook
	A	B
8	Total Target	%Growth/Week
9	10000	0.000%
10	20000	6.774%
11	30000	10.408%
12	40000	12.899%
13	50000	14.796%
14	75000	18.189%
15	100000	20.568%
16	200000	26.263%
17	500000	33.821%
18	1000000	39.631%
19	10000000	59.932%
Sheet1

Excel 2010

 

[rogerthat]

this is very helpful! thank you.. quick question: for your VBA code, do i need to have a formula in cell B5? and do i need to provide those same numbers you have from A9-A19? not sure how to replicate what you did in the setup before i run the VBA code.

i really wish there was a way to write out the actual formula that goal seek is using to figure out these %Growth/Week values. the reason is because let's say your goal is 500 for week 1 but you come in short the first week at 400. so you are 100 calls short of your goal. those 100 calls need to carry over to the next week which in effect should increase your %Growth for that week. So if your %growth from week 1 to week 2 was 20.6, but you came in short of your goal, then a formula would adjust the difference, and increase your %growth to lets say 20.7 for week 2, not 20.6. make sense?

the way we have it now, we'll have to use goal seek every time to adjust because we don't have the actual formula to do this. in any case, i'll take what i can get and do my best.. thanks again for taking the time to help. i know your time is valuable.

 

[circledchicken]

quick question: for your VBA code, do i need to have a formula in cell B5?

Yes, you need to have the formula =SUM(D2:W2) in B5.

and do i need to provide those same numbers you have from A9-A19? not sure how to replicate what you did in the setup before i run the VBA code.

No - you enter whatever numbers you want from A9 down (as many as you want) and the rates are generated from B9 down when you run the macro.

i really wish there was a way to write out the actual formula that goal seek is using to figure out these %Growth/Week values.

I don't think there is a formula Goal Seek is using. The Goal Seek algorithm progressively guesses for a solution - it is described nicely here:
http://support.microsoft.com/kb/100782

Note that it is not always possible to solve an equation for an explicit formula solution.

 

[shg]

+1 for CC's approach.

 

[circledchicken]

+1 for CC's approach.

Thanks!

 

[shg]

Here's a closed-form solution:

Function GeoRatio(ScaleFactor As Double, Sum As Double, nTerms As Long) As Double
    Dim x           As Double
    Dim dMin        As Double
    Dim dMid        As Double
    Dim dMax        As Double
 
    x = Sum / ScaleFactor
    If x <= 0 Then Exit Function
    If nTerms < 2 Then Exit Function
 
    Select Case Sgn(x - nTerms)
        Case 0
            GeoRatio = 1
            Exit Function
        Case 1
            dMax = Log(x) / Log(nTerms)
            dMin = 1
        Case Else
            dMax = 1
            dMin = 0
    End Select
    
    dMid = (dMax + dMin) / 2
    
    Do While SearchIndex(dMin, dMid, dMax, (1 - dMid ^ nTerms) / (1 - dMid) > x)
    Loop
    
    GeoRatio = dMid
End Function
 
Function SearchIndex(ByRef dMin As Double, _
                     ByRef dMid As Double, _
                     ByRef dMax As Double, _
                     bTooBig As Boolean) As Boolean
    ' shg 2011
 
    ' Changes the search limits dMin and dMax and
    ' the next test value dMid based on their
    ' current values and the results of the last test.
 
    ' Returns False when there are no more values to test.
 
    'Debug.Print dMin, dMid, dMax, IIf(bTooBig, "Too big", "Too small")

    If dMid = dMin Or dMid = dMax Then Exit Function
 
    If bTooBig Then dMax = dMid Else dMin = dMid
    dMid = (dMax + dMin) / 2
    SearchIndex = True
End Function

For your example,

=GeoRatio(500, 100000, 20)

 

[circledchicken]

Here's a closed-form solution:

Function GeoRatio(ScaleFactor As Double, Sum As Double, nTerms As Long) As Double
    Dim x           As Double
    Dim dMin        As Double
    Dim dMid        As Double
    Dim dMax        As Double
 
    x = Sum / ScaleFactor
    If x <= 0 Then Exit Function
    If nTerms < 2 Then Exit Function
 
    Select Case Sgn(x - nTerms)
        Case 0
            GeoRatio = 1
            Exit Function
        Case 1
            dMax = Log(x) / Log(nTerms)
            dMin = 1
        Case Else
            dMax = 1
            dMin = 0
    End Select
    
    dMid = (dMax + dMin) / 2
    
    Do While SearchIndex(dMin, dMid, dMax, (1 - dMid ^ nTerms) / (1 - dMid) > x)
    Loop
    
    GeoRatio = dMid
End Function
 
Function SearchIndex(ByRef dMin As Double, _
                     ByRef dMid As Double, _
                     ByRef dMax As Double, _
                     bTooBig As Boolean) As Boolean
    ' shg 2011
 
    ' Changes the search limits dMin and dMax and
    ' the next test value dMid based on their
    ' current values and the results of the last test.
 
    ' Returns False when there are no more values to test.
 
    'Debug.Print dMin, dMid, dMax, IIf(bTooBig, "Too big", "Too small")

    If dMid = dMin Or dMid = dMax Then Exit Function
 
    If bTooBig Then dMax = dMid Else dMin = dMid
    dMid = (dMax + dMin) / 2
    SearchIndex = True
End Function

For your example,

=GeoRatio(500, 100000, 20)

Wow, +10 for this approach!

 

[shg]

Bug . This line

dMax = Log(x) / Log(nTerms)

should be

dMax = x ^ (1 / (nTerms - 1))