400 students to grade, so little time...
 

I"ve collected all the marks for some 400 students and wished to convert them
from percentials into letter grades. Here is the breakdown for the letter
grades:

90 A+
85 - 89 A
80 - 84 A-
77 - 79 B+
74 - 76 B
70 - 73 B-
etc...

Now i have my percentages starting in L6 and would like to have the letter
grades starting @ M6.

I was thinking a nested if statement? please help!

--
A.

400 students to grade, so little time...
 

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.

Or see Chip Pearson's site for yet another take on a combination of the two
methods above.

http://www.cpearson.com/excel/excelF.htm#Grades


Gord Dibben MS Excel MVP

On Tue, 5 Dec 2006 13:59:01 -0800, Andmor
wrote:

I"ve collected all the marks for some 400 students and wished to convert them
from percentials into letter grades. Here is the breakdown for the letter
grades:

90 A+
85 - 89 A
80 - 84 A-
77 - 79 B+
74 - 76 B
70 - 73 B-
etc...

Now i have my percentages starting in L6 and would like to have the letter
grades starting @ M6.

I was thinking a nested if statement? please help!

Gord Dibben MS Excel MVP

400 students to grade, so little time...
 

Look in the help index for LOOKUP. There is a good example

--
Don Guillett
SalesAid Software

"Andmor" wrote in message
...
I"ve collected all the marks for some 400 students and wished to convert
them
from percentials into letter grades. Here is the breakdown for the letter
grades:

90 A+
85 - 89 A
80 - 84 A-
77 - 79 B+
74 - 76 B
70 - 73 B-
etc...

Now i have my percentages starting in L6 and would like to have the letter
grades starting @ M6.

I was thinking a nested if statement? please help!

--
A.

400 students to grade, so little time...
 

I thought the same Andmor until I read how many divisions you want. 2003 and
below is limited to 7 nested if statements (8 conditions). Your example has 6
conditions for just A & B.

Supposing average is entered in cell A1
in B1
=if(A1=90,"A",if(A1=80,"B",if(A1=70,"C",if(A1= 60,"D",if(<=50,"F",)))))
once formula is satisfactory fill down for all cells.

comma and ) placement are inportant, Must go from highest to lowest in this
case (lowest is always met). The example should get you started.
Lou

"Don Guillett" wrote:

Look in the help index for LOOKUP. There is a good example

--
Don Guillett
SalesAid Software

"Andmor" wrote in message
...
I"ve collected all the marks for some 400 students and wished to convert
them
from percentials into letter grades. Here is the breakdown for the letter
grades:

90 A+
85 - 89 A
80 - 84 A-
77 - 79 B+
74 - 76 B
70 - 73 B-
etc...

Now i have my percentages starting in L6 and would like to have the letter
grades starting @ M6.

I was thinking a nested if statement? please help!

--
A.

400 students to grade, so little time...
 

Gord,

I've converted it to fit my needs but am getting an error. Here is my
formula what am i doing wrong?.

=LOOKUP(L15,{
0,49,50,53,54,56,57,59,60,63,64,66,67,69,70,73,74, 76,77,79},{"F","D-",€�D€�,€�D+€�,"C-","C","C+",€�B-€œ,"B","B+",€�A-€œ,€�A€�,"A+"})

--
A.


"Gord Dibben" wrote:

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.

Or see Chip Pearson's site for yet another take on a combination of the two
methods above.

http://www.cpearson.com/excel/excelF.htm#Grades


Gord Dibben MS Excel MVP

On Tue, 5 Dec 2006 13:59:01 -0800, Andmor
wrote:

I"ve collected all the marks for some 400 students and wished to convert them
from percentials into letter grades. Here is the breakdown for the letter
grades:

90 A+
85 - 89 A
80 - 84 A-
77 - 79 B+
74 - 76 B
70 - 73 B-
etc...

Now i have my percentages starting in L6 and would like to have the letter
grades starting @ M6.

I was thinking a nested if statement? please help!

Gord Dibben MS Excel MVP

400 students to grade, so little time...
 

See response at other posting.

Gord

On Wed, 6 Dec 2006 09:19:01 -0800, Andmor
wrote:

Gord,

I've converted it to fit my needs but am getting an error. Here is my
formula what am i doing wrong?.

=LOOKUP(L15,{
0,49,50,53,54,56,57,59,60,63,64,66,67,69,70,73,74 ,76,77,79},{"F","D-",”D”,”D+”,"C-","C","C+",”B-“,"B","B+",”A-“,”A”,"A+"})

Gord Dibben MS Excel MVP