S-Curve

[PS25]

Hi,

I would like to make the data on the two sheets in the attached file in to an S-Curve of predicted growth of a group using absolute data. No data is personally identifiable so I can see if I am on track. I would like it to take in to account the fact it should grow quicker as time goes on.

Thanks in advance,

Peter

[Richard Buttrey]

Hi,

There seems a contradiction between wanting an 'S' curve and a curve that 'grows quicker' over time, by implication an exponential curve. An S curve implies a change of slope that rises/falls/rises which is inconsistent with an exponential curve.

Are you able to manually add some values that give you the sort of curve you're looking for so that we may see the goal.

[MrShorty]

If I may, I would suggest that this first needs to be a math problem before it can really be an Excel problem. You ask for an "S-curve", but there are many possible expressions that will yield an S curve. A 3rd order (or higher) polynomial can have an S-shape. There is a broad class of function called "sigmoidal functions" that have S shaped curves (https://en.wikipedia.org/wiki/Sigmoid_function ). I find that the first (and often most difficult step) for a problem like this is choosing an "S curve" function (which really isn't an Excel question at all).

The generic approach I use for something like this:

1) Choose a sigmoidal function.
2) Determine the parameters of the function from the input data. This is usually a regression step involving linear and/or non-linear regression algorithms, depending on the function chosen in (1).
3) Evaluate the result of (2) to make sure I get a reasonable result.
4) Use the result of (2) to perform the analysis, plot the chart, predict the future, or whatever I need to do with that information.

From there, I don't think I understand exactly what you want to do well enough to make any suggestions. Perhaps if you provide a more detailed example (complete with input information, desired output, and some sense of how you obtained the desired output), we will be better able to help you program that into Excel.

[PS25]

I would like a formula to workout the cells in yellow, the growth will not be linear it will be more exponential, how do I get excel to work this out?

[PS25]

I have tried to show what I would like however in May it shows I have gone over my target.

[MrShorty]

It's still not clear to me exactly what you want. The formula you have chosen (y=x*goal+496 where y is current goal, x is per cent of goal, and goal is Junes goal of 1500) will naturally yield a y greater than 1500 whenever x is greater than about 67%. Where did the values in "Goal Per cent" come from, since they appear to be hand entered? Are they fixed by some outside constraint? Or are these ultimately the values you need to determine? Considering that the starting percentage (496/1500~33%) is much more than thje 10% entered for January, what exactly does this "Goal Per cent" mean?

If I had to guess, I think what you want is a function that:
1) Increases continuously
2) the rate of increase initially increases to a maximum, then decreases.
3) the function approaches a maximum (usually asymptotically), but never exceeds that maximum.

All of which describes the logistic function mentioned in the Wikipedia article I referenced. Details are going to depend on exactly how you choose to implement the function. If you have no better recommendation, I would probably start this analysis using some form of the logistics function, then tinker with it from there until you get what you are looking for. Does that seem like a reasonable starting place, or do you have a recommendation for a starting place? If we use this as a starting place, can I assume you know how to program in the logistics function, or do you need help programming this function into a spreadsheet?

[PS25]

Hi

I have made a Formula that I am happy with which is =(the target %)*(the final goal) + (the start figure), see attached, if there is a better way please let me know.