5

I am trying to create a line graph in Excel that plots a student's actual progression against their predicted grade. The type of graph is called a 'flight path'. This is represented as two lines on the graph, one showing the actual grade students have received each term, and the second line showing their predicted grade at the end of their GCSE.

To show this, the graph requires that the student's National Curriculum level is shown on the primary (left) axis, and the GCSE grade on the secondary (right) axis. The problem has been customising the Y-axis labels to show actual grades (eg. 2a, 2b, 2c, or B-, B, B+ etc) rather than numbers. This is because I have had to convert the actual grades into arbitrary numerical values so that Excel can generate a graph in the first place. However, Excel then want to show the numerical values on the two Y-axes rather than the actual grades they represent.

So far, I have managed to customise the primary (left axis) to show National Curriculum levels by following the method related in this post...

Excel 2007 - Custom Y-axis values

However, my problem is that I cannot do the same for the secondary axis (right) axis using the same method. When I do, the GCSE grade labels still appear on the left (primary) axis when I want them to appear on the right.

*Screenshot showing current flight path table in Excel with right (secondary) axis showing numbers instead of grades G-A**

As you can see from the screenshot above, currently, my right (secondary) axis simply reads 0-10 whereas it should read G-A* (with the 'G' grade label) starting at where number '2' is currently found).

Could anyone help? I am aware that I could simply overlap the secondary axis with a textbox, but I would prefer the labels to integrated into the graph itself in case it ever needs resizing. Any help would be greatly appreciated!

EDIT

OK - just tried something that nearly worked. I switched the graph type from line to scatter. This has resolved the problem of customised data labels on the right-hand (secondary axis) because it has replaced the x-axis labels with numbers. This has allowed me to specify the GCSE grades co-ordinates along the -x-axis, but has of course replaced the date labels with numbers. The screenshot is below...

enter image description here

Argh! Any ideas? :)

Improve this question
2
  • This should be relatively simple to accomplish. Can you post a screenshot or file to a file sharing site (like dropbox)? Then someone with enough rep can edit into your question.
    – dav
    Aug 31, 2015 at 11:51
  • Hi dav - thank you so much for the quick reply! I've managed to accrue enough rep now to upload a screenshot of the graph as it currently stands. As you can see, I've managed to customise the left axis to show National Curriculum grades, but the GCSE grades need to appear on the right. I can make GCSE grades appear on the primary axis, but not the secondary by following the steps explained in the link above. I'd really appreciate any ideas that you might have!
    – Northerner_V1
    Aug 31, 2015 at 13:26

1 Answer 1

Reset to default
4

Done it!

The graph needs to stay as a scatter graph. The horizontal needs to have a third dummy table set up with all of the y values set to zero. This effectively maps the labels onto the horizontal axis using the same technique as on the primary and secondary axes. I realise this post has pretty much been a dialogue with only one voice speaking, but if anyone else is attempting a similar graph in Excel I hope it helps you.

Here is the finished result...

enter image description here

Hope it is helpful to you. Its taken me just under a week of work to get it finished!

Improve this answer
1
  • 1
    It's great when someone talks through the problem and figures it out themself. Even better when they post how they did it as an answer.
    – Jon Peltier
    Sep 10, 2015 at 12:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .