How do a I use a spread sheet to track grades?

[Guest]

I'm trying to use Excel, which I know is possible, to set up a grade book
without using a template. I can enter students and points, I just don't know
how to get it to average and give a letter grade. I don't want to download a
template because I know there's a way to do it without one.

 

[Gord Dibben]

Nicole

To get an average use the AVERAGE function as in =AVERAGE(A1:A50)

To convert scores to letter grades use a Lookup table and VLOOKUP functions.

OR a Lookup formula without a table

Assuming scores are in column A starting at A1.

In B1 enter this formula then drag/copy down column B

=LOOKUP(A1,{0,31,41,51,61,71,81,91,101},{"E","D","C-","C","C+","B","B+","A"})

Example only. Adapt for your scores and grades.

Note the curly braces internally.


Gord Dibben MS Excel MVP

 

[Guest]

This might help!

let's assume you have test score in A1:A10, you would use =AVERAGE(A1:A10)
in B1 to get an average score, then in another cell you would put
=LOOKUP(B1,{0,55,60,65,70,75,80,85,90,95,100},{"F","D","C-","C","C+","B-","B","B+","A-","A","A+"})
to get a matching grade.

{0,55,60,65,70,75,80,85,90,95,100}..... these scores will trigger a
different letter grade...... i.e a score of 63 would be C-, a 50 an F and so
on.
and
{"F","D","C-","C","C+","B-","B","B+","A-","A","A+"}...matching letter grades

of course the scores and matching letter grade are just made up so you would
need to ajust them. Just make sure you have the same number of entry in each
brackets.

Hope this help!
JG

 

[mevetts]

Can it be done so a range of numbers applies to a certain grade?

Example -

50-55 = C
56-60 = B
61-100 = A

 

[Guest]

Yes, all you need to is alter the lookup value and corresponding grades.
=LOOKUP(A1,{0,50,56,61},{"D","C","B","A"})

HTH
JG

 

[mevetts]

Sorry, I'm being a bit dumb, please could you show me an example?

 

[Guest]

Well for example you have a range of numbers in A1:A4

A1 - 84
A2 - 50
A3 - 62
A4 - 46

by using this formula in B1 =LOOKUP(A1,{0,50,56,61},{"D","C","B","A"}) and
copying down to B4, you would get grades of

B1 - A
B2 - C
B3 - B
B4 - D

numbers in the first brackets represents the starting number for a
corresponding grade in the second bracket.... hence from 0 to 49 you get a
grade of "D", from 50 to 55 you get "C", from 56 to 60 an "B" and from 61 on
up an "A".

Does that help?
JG

 

[mevetts]

Yes, thanks. I didn't realise it worked from the initial integer up to
the next, clever.