Align Line Charts By Milestone Dates?

[Guest]

I'm a chart neophyte.

I have a worksheet with multiple line charts, each line depicting total
hours per month per project.

I would like to align the lines to a common milestone, like "start of
detailed design". Each project of course has a different date for this
milestone.

Other than knowing that somewhere I'll have to specify the dates of the
milestone for each project, I have no idea of what to do.

Any help/insight will be appreciated.

Thanks,
Mike

 

[Jon Peltier]

Each data series has a column of dates and a column of percent complete
values. I'll use columns A and B in this example. Insert a couple rows so
that the headers are in A3 and B3 and the data starts in row 4. Insert a
column between A and B. In B1 enter the date of reference. In B3 enter a
header like 'Elapsed'. In B4 enter this formula

=if(A4<B$1,-B$1-A4,A4-B$1)

Format as a number (not a date) and drag to fill as much of column B as you
need. Select the data in B:C and create an XY chart (marker with line or
just line).

- Jon

 

[Guest]

Jon:

So B1 is my milestone date, the IF statement in the new column gives me my
"alignment" and my new common axis for the new charts.

Thanks!
Mike

 

[Guest]

Jon:

I've gotten your suggestion working fine, for each individual series.

But I'm running into trouble trying to "overlay" the series onto the same
chart. For one series the X axis (Elapsed) ranges from 29-425, and the range
for the other series is 176-572. The X axis ends up coming from whichever
series I add last.

How do I put these on a chart where the X axis is say, 0-600, and the series
lines begin and start on the appropriate "Elapsed" values?

Thanks again,
Mike

 

[Guest]

Ha! Figured it out. The answer is, Scatter chart.

Thanks!

 

[Jon Peltier]

The X axis ends up coming from whichever series I add last.

Don't make a line chart, make an XY chart. In a line chart, all series share
the same X values, and the "numbers" are treated as nonnumeric labels.

- Jon