Difficult chart to compose?

[Guest]

I would like to make the following chart but don't know how to do this.
Data:
- for 5 persons I have values of a variable Y at different stages of
development.
- the number of stages of development that are distinguished differs between
the 5 persons (3, 4, 5 or 6 different stages).

The chart:
-The chart should show a line for each person with horizontally the stage of
development and vertically variable Y (so 5 lines).
-The line for the person for which 3 stages are distinguished should start
all the way to the left of the figure and stop all the way to the right of
the figure. So should the line for the person for which 6 stages are
distinguished. The only way, I can think of to do this, is create 30 stages
(a multitude of 3, 4, 5 as well as 6) and spread the values over these 30
stages so that all lines start all the way to the left and end all the way to
the right of the chart.
- The absolute numbers are not of interest. Variable Y is measured very
different for the different persons (and cannot be brought back to the same
unit of measurement). It is only the trend that is of concern. How can I take
this into account for the chart?

Thanks for any advice, in advance.

 

[Del Cotter]

I would like to make the following chart but don't know how to do this.
- for 5 persons I have values of a variable Y at different stages
- the number of stages of development that are distinguished differs between
the 5 persons (3, 4, 5 or 6 different stages).

Don't use a line chart, as getting the categories right will, as you
say, require a number of categories that has all of the different
possible number of stages as factors (30 has factors 3, 4, 5 and 6).

Instead use an XY chart. Construct the chart initially with the same
x-axis for your convenience, then go in and change the x-axes for all
the persons to be a different x-axis. You are now free to have each
person spread across their own range from left to right.

For 3 stages: 0%, 50%, 100%
For 4 stages: 0%, 33%, 66%, 100%
etc.

To be really fancy, you could make the x-axis a "named range" (actually
a misleading name for a much more powerful facility to create named
functions) to calculate the spread based on a cell reading "3" or "4" or
so on. But this is more fancy than you need just now.

 

[Guest]

"then go in and change the x-axes for all the persons to be a different x-axis"
Thanks for your reply. Could you specify how you can to the above in Excel?

 

[Jon Peltier]

You're not changing the axis. Del means change the values used on the X
axis. Select the chart, go to Source Data on the Chart menu, and click on
the Series tab. for each series, make sure that the X values points to the
correct range for that series (i.e., to {0%,50%,100%} for the series with 3
points, {0%,20%,40%,60%,80%,100%} for the series with 6 points), and that
the Y values points to the correct range. As Del points out, be sure to use
an XY chart, not a Line chart.

- Jon