Assigning numeric values to text in excel

  Rich84 17:46 27 Oct 05

Can anybody help with what seems like a simple wish, but is proving hard to realise? I have a spread sheet for keeping track of student's grades which are in the form of 1c, 1b, 1a, 2c, 2b, 2a, 3c, etc. I want to average them and so must allocate a numeric value which can be averaged and then converted back to the above form. Can anyone tell me how to do this?
Sorry if this is not an appropriate query for this forum. If not any thoughts where I might try. Thanks.

  VoG II 18:28 27 Oct 05

What numeric values do you want to assign to 1c, 1b, 1a, 2c, 2b, 2a, 3c, etc.?

  Rich84 19:09 27 Oct 05

Starting with 1c=9 and then in units of 1. 9, 10, 11, etc.

  VoG II 19:21 27 Oct 05

I think that you need 2 lookup tables, one to convert from grades to numbers and the other from numbers to grades.

In the first table you would have grades in Column A and the numbers in Column B (say sheet2, A1:B10). In the second table these would be reversed (say sheet2 A21-B30).

In the sheet where you want to do the calculation you would need an extra column that you would use to lookup the number that you want to perform calculations on. So if you have a grade in cell C2 then in D2 the formula would be along the lines of:

=VLOOKUP(C2, 'Sheet2'!A1:B10, 2, False)

When you have the result of your calculation (in say F2) then in G2 the following will return the nearest matching grade.

=VLOOKUP(F2,'Sheet2'!A21:B30, 2, True)


=VLOOKUP(Value to lookup, Range of lookup table, Column number of lookup table to return value from, True or False).

Setting True returns the nearest match, False returns an exact match (or an error if there is no match).

  Rich84 18:49 29 Oct 05

Thanks Vog. Managed to get it working eventually.

This thread is now locked and can not be replied to.

Apple Event live stream | Apple October Event live blog: Watch Apple unveil new MacBook Pro, new…

1995-2015: How technology has changed the world in 20 years

Lida designs a cross-stitch email sent to thousands of Ikea Family members

Apple Watch 2 review | Apple Watch Series 2 review: Faster performance, a brighter screen, water-ret…