Hi guys,
I'm after a little help from anybody who know excel well enough to help me!

Basically I'm trying to set up an assessment spreadsheet for the school I work in. I have a list of the childrens grades which I then need to convert into numbers. The grades are not just letters, but are a number followed by a letter.

For example -
When a teacher inserts the grade of 3b, in the column to the right of that I need it to populate the number 21. I can then use the numerical scores to create an average for that yeargroup and convert that into an overall level for said yeargroup.

Can anybody help me?

Thanks.

Nick.

 

Sounds like a lookup table to me, populated with all the equivalent values, then the average calculated and if necessary converted back to a grade.

Probably a more sophisticated method, but that would work.

 

You need to set up a table of Grades and Grade Scores in a new worksheet tab in the spreadsheet and then use the VLOOKUP command:

1. Set up a table caled GradeScores on a new worksheet tab e.g.

A.......... B
1 Grade Score
2 1a 95
3 1b 80
4 2c 55
Etc.

2 Name the table

- Highlight all the table entries (columns A & B)
- Use the Insert / Name / Define to give your table a name e.g GradeScores


3. Go back to the Assessment worksheet and put in the table lookup command in the cell next to the grade

VLOOKUP(pupil’s grade cell ref,GradeScores,lookup table column number,0)

e.g. if the Assessment table was:

A ........B .......C
1 Pupil Grade Score
2 Smith, J 1b 80
3 Jones, J 2c 55
4 Khan, J 1a 95
Etc.

Then column C2 would contain:
VLOOKUP(B2,GradeScores,2,0)

My tabs stops don't show the rows and columns very well but hope this helps.

Sam

 

That's the one, knew I hadn't lost it completely since I retired