# Can I give text a numerical value in excel

I am sorting pupils grades, and would like to show their progress in a line graph. The only problem is that the grades are not numerical.

I would like it so that whenever I put in the grade, it is assigned a numerical Value for the chart.

So, the lowest grade is P1c So would like that to be (1) then P1b (2) P1a (3) P2c (4) etc.

Is this possible?

This is what I have tried so far

Then the look up is on a seperate tab

Look up table

• This can be done with `VLOOKUP`. See here for more info – cybernetic.nomad Dec 3 '18 at 15:39
• I have made a look up table but the formula keeps coming up as an error – Sarah Pierpoint Dec 3 '18 at 16:19
• Can you please edit your question to give us the formula you are using? Sample data may also be useful – cybernetic.nomad Dec 3 '18 at 16:22
• Added my screen shots! – Sarah Pierpoint Dec 3 '18 at 16:30

You need to change your formula to:

`=VLOOKUP(D13,'Look up'!A1:B27,2)`

`VLOOKUP` parameters are

(lookup_value, table_array, col_index_num, [range_lookup])

---{where range_lookup is optional}

You had `FALSE` as the `col_index_num` parameter, which equates to `0`, instead of `2`. The second column of your `table_array` range is where you want to look for the numeric equivalent to your grade. How it works:

• Write this Formula in cell `G127` to get Score for respective Grades in `Sheet 1` & fill it down.

`=IFERROR(VLOOKUP(F127,Sheet2!A2:B28,2,FALSE),"")`

• Select the Blue line in Graph's Plot area & Right Click the Mouse.
• Click Add Data Labels & finish with Ok.
• Click anywhere in Graph's Plot area and click Right Mouse & hit Select Data command from menu.
• From Select Data Source dialog click Add button & for Edit Series Input Box select `F127:F134` (Grades in Alphabets).

You find the Graph, is looks like shown in Screen Shot.

Adjust cell references in the Formula & for Graph Data Series as needed.