Excel help required please

[eric]

Hi

A little assistance is required please.

I have about 2000 grades in a table, and I need to change the grades to point scores.

So for example

A=8
B=7
C=6

and so on.

Now I want to write a formula to change all the grades to their points equivalent.

I have tried vlookup, but am not having success at the moment.

Could anyone point me in the right direction please

Thanks

[PTBurkis]

You could run a VBS script which would transpose the data into a second sheet:

sub grades

dim colcount, rowcount as integer
dim currentgrade as string
dim gradescore as integer

for colcount = 1 to <number of columns>
for rowcount = 1 to <number of rows>
currentgrade=sheet1.cells(colcount,rowcount)
select case currentgrade
case "A"
gradescore=10
case "B"
gradescore=8
case "C"
gradescore=6
case "D"
gradescore=4
End Select
sheet2.cells(colcount,rowcount)=gradescore
next rowcount
next colcount

end sub

[phollan1]

Hi

A little assistance is required please.

I have about 2000 grades in a table, and I need to change the grades to point scores.

So for example

A=8
B=7
C=6

and so on.

Now I want to write a formula to change all the grades to their points equivalent.

I have tried vlookup, but am not having success at the moment.

Could anyone point me in the right direction please

Thanks


You could use the vlookup function create a small table with the like for like scores to grades and you can then hide that column and display the results in a new column, off the top of my head can't remember the sytax but you can find it in help and do it form there

Paul

[Andrew70]

Create a new tab on your spreadsheet.
Starting in Cell A1, enter your grades and points
A1 = A, B1 = 8,
A2 = B, B2 = 7, etc.
When you have finished creating this table, highlight it from top left to bottom right so that the blue selection area covers all the data.
Using the word menu at the top of the page, click INSERT, NAME, DEFINE.
Give the selected data range a name - something short and relevant like CONVERT.
Go back to your initial spreasheet.
Insert a new column next to the one with your A B Cs in.
I'm going to assume the existing and new columns are B and C and the data starts on row 1.
In C1 type this formula: =vlookup(B1,CONVERT,2,false)
Copy it down as far as you need to. If you want to fix the results as values rather than leave them as formulas, copy the range, then use the word menu at the top and click EDIT, PASTE SPECIAL, and put the dot next to VALUES in the paste section, then click OK.
If you leave them as formulas you need to leave the second tab in place. If you convert them to values you can delete the second tab.

[R2-D2]

If you're sure you've just got grades from A to I (8 to 0 points) then you could use this formula to convert the grade in cell A1:=73-CODE(UPPER(A1))