Converting results

[simmo]

Hi

I have a spreadsheet that records results for school tests. The results are
then converted to grades i.e 2a,2b,2c,3,3a,2b etc. These are then plotted
on graphs in a varity of ways. To make it work I manually assign a number
to the grades in a seperate column i.e 3b becomes a five I can then use this
to create the charts. Can I set up the column to read the original grade
and return the number. I have tried look up tables but seem to fail any
hints on how to get it to work would be appreciated.

Thanks

Simeon

 

[Robert Rosenberg]

Not sure how many grades you have but a lookup function and a table should
handle this for you.

1. Create a two column table, placing all the grades in one column and all
of the plot numbers into another, like so...

G H
Grade Plot Number
2a 4
2b 4.5
2c 4.75
3 5
3a 5.25
3b 5.5
.. .
.. .
.. .


2. In the cell next to each of the converted grades, you use a formula
similar to the following:

=VLOOKUP($A5,$G$2:$H$50,2,FALSE)

Where:

$A5 = the converted grade
$G$2:$H$50 = range containing the list described above
2 = the column within the list above that contains the plot number
FALSE = Do NOT look for the closest grade - Look for an exact match

3. Copy the LOOKUP formula down the column for each converted grade

 

[Tom Ogilvy]

on another sheet in column A and B

2 1
2a 2
2b 3
2c 4
3 5
3a 6
3b 7
3c 8

=vlookup(A1,Sheet2!A1:B8,2,false)

Where the table is in Sheet2, A1:B8

A1 holds one of the grades.

Use the above as a model for your actual table.

 

[Gord Dibben]

Simeon

Basic example of VLOOKUP

With grades 2a, 2b, 2c etc in column A and numbers 1, 2, 3 etc in Column B

In D1 enter =VLOOKUP(C1,$A$1:$B$10,2,FALSE)

Enter a grade, say 3b, in C1 and D1 will return 5

This formula can be dragged down Column D if need be.

The list range of A1:B10 can be adapted to your actual range.

Gord Dibben XL2002

 

[simmo]

Thank you for all your help you have saved me a lot of time.

Simeon