# Building S-Curves for projects in Excel using functions on dates and expected completion percentages

I have some data about projects that are guesstimated. This is a simple management of many unmanaged projects.

A project has a start and an end date, then a grid with Date, Planned %, and Actual %.

The planned is zero at the begin date and 100% at the end date, I'm trying to have an S-Curve in my graph with calculations from the begin date, the end date, and the date column.

I tried many EXP and LN functions, some trigonometric function, but nothing looks right.

Is there a formula I can plug into the cells in the "Planned" column to get a curve that makes sense?

• Why not make a set of data : the example s curve you show looks about right , then just scale that data with the info ie start and end dates that you know. Have you tried the forecast function? Commented Nov 12, 2017 at 19:24
• That draws a straight line with points at 0%,50%, and 100%. Commented Nov 12, 2017 at 19:57
• So, you made a set of data to show that s curve, then you get a straight line... Commented Nov 12, 2017 at 20:10
• I'm with Mike - use whatever you used to get the graph you've shown. Otherwise, the integral of the normal distribution gives a nice S-shaped curve. It's a built-in function in Excel called the Cumulative Normal Distribution, or something close to that. It isn't available for use as a data fitting function, you'll have to plot it separately, and scale it appropriately. Ask a question about "least squares fit" if you don't know how to do that. Commented Nov 13, 2017 at 4:04
• added an answer as a community wiki... Commented Nov 13, 2017 at 7:33

If the start to end date were scaled to numbers between 0 and 1, and using `=1/(1+EXP(-(X*12-6)))` I get a nice exponential curve that was too narrow (blue curve).
Modified it as `=1/(1+EXP(-(\$B4*12-6)*\$D\$1)*\$D\$2)` but it started at 4.74% and ended at 95.26%. (orange curve)
• A well known and fairly simple equation for a sigmoid curve is the Logistic function `=A/(1+exp(-k*(x-x0))`. A is the maximum value, k is the steepness of the curve, and x0 is the midpoint. You got close by reinventing the wheel, but the function is simpler. Commented Nov 13, 2017 at 16:20