ExcelBanter - alphanumeric data chart

Quote:

Originally Posted by Tarren41 (Post 963310)

Hi all.

I'm trying to put together a spread sheet that records the progress of pupils through the year in 3 subjects over 3 terms.

I'd like to create a line chart for each pupil showing a line for each subject, Y axis as level and X axis as term (1,2 & 3).

The problem i'm having is that the levels are alphanumeric: low to high in the following order:
1c - 1b - 1a - 2c - 2b - 2a - 3c - 3b - 3a.

Any help with this will be gratefully recieved.

Thanks.

Unfortunately I can't think of a way to get Excel to recognise a1, a2...c3 as a sequential order of values and chart them accordingly. Your best bet is to replace them (in another worksheet if required) with numeric values and chart these instead (with a key to reference back to).

In this way a1=1, a2=2, a3=3, b1=4, b2=5, b3=6, c1=7, c2=8, c3=9

NB: this doesn't mean you have to lose your a1, a2...c3 grades, keep those and use the formula below in a new sheet.

To automatically replace the marks in your workbook with numeric values, you could do a VLOOKUP where you have a1, a2, ...c3 in column X and 1, 2, ...9 next to each in column Y.

If Columns A, B and C hold the marks for the 3 subjects, in a new cell, or even worksheet, put in the formula:
=VLOOKUP(A1,$X$1:$Y$9,2,FALSE)

This will look in cell X for a value matching A1 and return the corresponding number held in column Y. Copy and paste this formula for each mark you have assigned on your original worksheet and each mark should be replaced with a number between 1 and 9 (anything outside of the a1, a2,...c3 will return a #N/A error).

Once you have all of the numeric values on a new worksheet, you can plot these in a chart instead, and just create your own key to what the values 1-9 mean (or paste a text box over the Y-axis with the grades instead).