Converting results

  • Thread starter Thread starter simmo
  • Start date Start date
S

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
 
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
 
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.
 
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
 
Back
Top