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

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.

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


On Tue, 10 Jan 2006 16:16:03 -0800, "NicoleLT06"
<[email protected]> wrote:

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

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



"NicoleLT06" wrote:

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

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" wrote:

>
> Can it be done so a range of numbers applies to a certain grade?
>
> Example -
>
> 50-55 = C
> 56-60 = B
> 61-100 = A
>
>
> --
> mevetts
>
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=499985
>
>

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" wrote:

>
> Sorry, I'm being a bit dumb, please could you show me an example?
>
>
>
> --
> mevetts
>
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=499985
>
>