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?
51 Answer
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.
