Averaging a series of written text grades i.e. Dist*, Dist, Merit,Pass and Fail

[didi]

I want to be able to average a series of written text grades i.e.
Dist*, Dist, Merit, Pass and Fail from cells that have already been
populated with these grades. I want it to work that if a student gets
a Dist, Merit and a Pass then overall they get a Merit. This sum will
have to average a range of up to 6 different grades starting from if
there is only one grade currently in the range i.e. i have only
written reports for term 1. I am currently doing this in Excel 2003
but do have access to Excel 2007 should this be easier in the more
recent version.

Hope you can help.

Thanks x

 

[Pete_UK]

Can you describe your data layout with a few examples? I imagine you
have students' names in one column going down the sheet, and then use
other columns to record the grades achieved going across the sheet.
Which columns do you actually use?

What other rules do you want to apply? If a student gets 2 merits, but
no distinctions, will this still give an overall merit? How many
passes (or better) will count towards a Pass grade?

Pete

 

[didi]

Can you describe your data layout with a few examples? I imagine you
have students' names in one column going down the sheet, and then use
other columns to record the grades achieved going across the sheet.
Which columns do you actually use?

What other rules do you want to apply? If a student gets 2 merits, but
no distinctions, will this still give an overall merit? How many
passes (or better) will count towards a Pass grade?

Pete





- Show quoted text -

Pete,

The way that it is set-up at the moment is that there is the students
name in column A with other details on them in columns B to K. In
column L there is the current grade which is where the result of the
avergae result needs to be returned to. In columns M to R are the
grade results for each term which are pulled from a set of different
cells, i have after thinking that it might be easier to pull the data
from a range of cells next to each other.

Basically for the rules it will follow as:

Distinction = 12 points
Merit = 8 points
Pass = 4 points

And for the grading it will be:

Total no of points 98-120 = Distinction*
Total no of points 84-96 = Distinction
Total no of points 60-82 = Merit
Total no of points 36-58 = Pass
Total no of points <36 = Fail

Obviously the number current grade won't reflected the actual grade
until all 6 of the grades won't be filled in, so looking at the
problem it is not now a case of averaging the grades but assigning
them values and assigning the current grade with an if statement to be
able to work out the current grade?

Diane

 

[Pete_UK]

Instead of multiple IF statements, you can use VLOOKUP to convert
those grade descriptions to numbers. Set up a little table somewhere,
eg in X1:Y4 like this:

Distinction 12
Merit 8
Pass 4
Fail 0

Then with a grade in M2 you can change that to the appropriate number
with this:

=VLOOKUP(M2,$X$1:$Y$4,2,0)

To do the same with the grade in N2, you just need to change the M2 to
N2, and so on.

Hope this helps.

Pete