2

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 %.

excel sheet

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.

S-Curve 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?

5
  • 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?
    – Solar Mike
    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...
    – Solar Mike
    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

1 Answer 1

1

Thanks to all the commenters, the curve I had was an image from Wikipedia.

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)

Scaling a bit more makes it much better (green curve). Projects start slowly, progress, then end slowly. Comparing this curve with a smoothed estimated completion percentages curve makes sense.

S-Curves in Excel

2
  • 2
    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
  • This makes sense, I was tweaking multipliers and using complicated "tricks"... Commented Nov 14, 2017 at 21:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .