Getting the right chart legend

[JenniferMurphy]

I have a table of proficiency ratings (0-1) showing how they can be converted to an inverse scale with a damping factor. The headers in Row 3 show the factors. [TABLE="class: grid, width: 239, align: center"]
<tbody>[TR]
[TD="align: center"]C/R[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]F=0.5[/TD]
[TD="align: center"]F=1.0[/TD]
[TD="align: center"]F=2.0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0.90[/TD]
[TD="align: center"]0.32[/TD]
[TD="align: center"]0.10[/TD]
[TD="align: center"]0.01[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]0.80[/TD]
[TD="align: center"]0.45[/TD]
[TD="align: center"]0.20[/TD]
[TD="align: center"]0.04[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]0.70[/TD]
[TD="align: center"]0.55[/TD]
[TD="align: center"]0.30[/TD]
[TD="align: center"]0.09[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]0.60[/TD]
[TD="align: center"]0.63[/TD]
[TD="align: center"]0.40[/TD]
[TD="align: center"]0.16[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]0.50[/TD]
[TD="align: center"]0.71[/TD]
[TD="align: center"]0.50[/TD]
[TD="align: center"]0.25[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]0.40[/TD]
[TD="align: center"]0.77[/TD]
[TD="align: center"]0.60[/TD]
[TD="align: center"]0.36[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]0.30[/TD]
[TD="align: center"]0.84[/TD]
[TD="align: center"]0.70[/TD]
[TD="align: center"]0.49[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]0.20[/TD]
[TD="align: center"]0.89[/TD]
[TD="align: center"]0.80[/TD]
[TD="align: center"]0.64[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]0.10[/TD]
[TD="align: center"]0.95[/TD]
[TD="align: center"]0.90[/TD]
[TD="align: center"]0.81[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]1.00
[/TD]
[/TR]
</tbody>[/TABLE]

I want to graph this table to show a line for each column (D, E, F). If I select C4:F14, I get the correct graph, but the legend labels are Series 1, Series 2, & Series 3.

If I select C3:F14, the graph is all screwed up. The X axis goes from 0 to 14, I assume because it takes Col B to be the X values, even though I didn't select it. The Y axis goes from -0.5 to 2.5, I assume because it takes Row 3 as data. And the legend still says Series 1 to Series 4.

The actual data in D3:F3 is 0.5, 1.0, & 2.0. The headers are generated using a custom format. This allows me to use those cells in the formulas below. But even it I get rid of the custom formats and just have the numbers in those cells, the graph looks the same including the legend.

Is there a way to get a graph that uses Row 3 as the labels, Col C (C4:C14) as the X values and Cols DEF as the Y values?

PS: I would post the graphs themselves, but I don't know how to do that without saving then to a file and then uploading that to Dropbox or somewhere? Why can't I just paste an image?

 

[Trouttrap2]

You can try this. First delete the cell contents in C3, meaning the R. Then highlight the C3:F14. That should put the X axis values found in C3:C14 and the series should be F=.5, F=1.0, F=2.0.

 

[JenniferMurphy]

You can try this. First delete the cell contents in C3, meaning the R. Then highlight the C3:F14. That should put the X axis values found in C3:C14 and the series should be F=.5, F=1.0, F=2.0.

Wow, that worked. What's the rule? Am I not allowed to have a title over the X column? I kinda need it for when I paste the actual table in a document along with the graph.

Thanks very much.

 

[JenniferMurphy]

PS: I just discovered that I can replace the "R" after the graph is created. So do I need to delete it just long enough to create the graph?

 

[Trouttrap2]

Yes, you can put the R back. I should have said so. Anytime you are making a line chart Excel needs to know what the X axis is going to be. One quick way is leaving the Xaxis column header blank.

 

[JenniferMurphy]

Yes, you can put the R back. I should have said so. Anytime you are making a line chart Excel needs to know what the X axis is going to be. One quick way is leaving the Xaxis column header blank.

It would be handy if Excel would assume that the first column is the independent variable and use the header as the variable name. In this case, it would become the R axis.