Graph for Alpha-Numeric figures

[ianh]

I have a scale (key stage 3 sub-levels) 3c, 3b, 3a, 4c, 4b, 4a, 5c, 5b, 5a.
Each student has a start and end level and i would like to have a bar between the points in the scale:
eg

Student, Start level, End Level
StudentA, 3a, 4c
StudentB 4c, 6a

[DonkeyOte]

You might consider using additional cells to hold numerical equivalents, eg:

Please Login or Register  to view this content.

where the first numerical value is D2

Please Login or Register  to view this content.

You could then Col A (studen), Cols D & E (numeric Start/End) as source for your chart.

[ianh]

I'd considered using a lookup just to convert the values into a number in the scale then using the 3a etc as data labels on the graph to show the points.

I would like the graph to have looked something like the attached file if possible

[DonkeyOte]

I'm no charting wiz - on that note I've moved your thread to the Charting forum so those that are can help you further.

Attached is a very basic approach to the chart you outline in your image - not sure if it helps or not.

edit: 20:51 UK time - reloaded attachment (original missing formulae)

[DonkeyOte]

Attached is an updated version which combines the stacked column with an XY scatter for the purposes of a custom axis.
Hopefully Andy Pope and co. can provide you with a better approach

[Andy Pope]

I went with a Bar/Stacked Column combination.

The Bar chart provides the axis for the Grades.
The Stacked column chart provides the floating bars and Student labelling.

I also used a VLOOKUP for the grade to chart value conversion.

[DonkeyOte]

Thanks for helping Andy

This was my first attempt at this type of chart and one thing that frustrated me was that I needed to add the surplus row of data (3) to get the bars to show in full with the Axis position set at "On Tick Marks" - if set as "Between" then the axis showed inside the chart.

I see on yours this isn't necessary and I'm pretty sure I went wrong pretty early on in my approach but continued to hack my way around until I got close

[ianh]

Thanks Guys.