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

microsoft excelmicrosoft-excel-2010project-managementworksheet-function

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?

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

Related Solutions

Excel graph plotting for large data set with dates

To solve number 2 (Selecting all the data in the column):

Select the first cell you want and then use the keyboard shortcut Ctrl + Shift + Down Arrow

It will stop at the next blank cell, so if you have blanks in the middle just keep using the shortcut.

Excel – Tips for Building and Managing Complex Functions

Here's a bunch of main rules I use on my own and suggest to for staff during trainings:

Build complex formulas (especially IFs) part by part (or argument by argument), i.e. if you have a column of data you want to parse according to certain condition, start with simple =IF(LEFT(A1,2)="AB",1,0) - thus you're sure the condition works as expected and returns correct results for each type of values. Further you replace each argument by its own calculations (see below), but such way you're sure that any error / wrong result is NOT caused by wrong condition itself, otherwise errors may interfere and overall formula results are totally unexpected and extremely hard to correct.
Each argument is prepared in separate column as independent formula, and only if it works, it is merged to main formula (or megaformula as it is called in some Excel Bibles).
Use hardcoded values as intermediate solutions. E.g. if you're building a dynamic chart, range is usually calculated using OFFSET and COUNTA functions. So, firstly build your OFFSET using pure numbers, e.g. =OFFSET(A1,0,0,1,10), make sure it returns what you really want, and only then start building calculated part, e.g. =OFFSET(A1,0,0,1,COUNTA(A:A)-1).
Do not neglect formula evaluator (Ribbon Formulas > Evaluate Formula): for people who see it for the first time it's like a ray of light) Indeed, having correct syntax does NOT mean in general having expected / correct results, and that's like Excel debugger.
To improve readability of complex formulas, you may use new line separations (may be added via ALT+ENTER in formula bar) as well as extra spaces to make them look like a code with indents (all these are ignored when formula is evaluated):
```
=IF(
    LEFT(A1,2)="AB",
       MONTH(TODAY()),
       MONTH(B1)
   )
```

I hope these suggestions will make life easier) Sure other answerers will add some more useful points.

Best Answer

Related Solutions

Excel graph plotting for large data set with dates

Excel – Tips for Building and Managing Complex Functions

Related Question