boolean formula for grades...help!

[Kevin]

Can anybody solve this problem?
I am a teacher who created an electronic grade book on
Excel. I have a formula to calculate both the student
and the class averages.

Now, I need help with a formula to convert the percentage
into a letter grade!
For example: if (cell) <=59, then F, else if (cell) <=69,
then d, else if (cell) <=79, then B, else if (cell) <=89,
then B, else if (cell) <=100, then A

I've tried several variations of this numerical to text
conversion, but to no avail!

Please help with any suggestions.
Thanks, Kevin

 

[Norman Harker]

Hi Kevin!

Use:
=VLOOKUP(A1,$G$1:$H$5,2)

In G1:H5 I have the table:
0 F
60 D
70 C
80 B
90 A


You can put your table within the function but it's a lot easier to
make changes to it if you give it a separate area of the sheet.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Roger]

Kevin,

Try the following formula:

=IF(B6<60,"F",IF(B6<70,"D",IF(B6<80,"C",IF
(B6<90,"B","A"))))

Roger

 

[Ron Rosenfeld]

Can anybody solve this problem?
I am a teacher who created an electronic grade book on
Excel. I have a formula to calculate both the student
and the class averages.

Now, I need help with a formula to convert the percentage
into a letter grade!
For example: if (cell) <=59, then F, else if (cell) <=69,
then d, else if (cell) <=79, then B, else if (cell) <=89,
then B, else if (cell) <=100, then A

I've tried several variations of this numerical to text
conversion, but to no avail!

Please help with any suggestions.
Thanks, Kevin

Well, only because you specified "boolean" :

=CHAR(MIN(70,74-MIN(9,INT(grade/10))*(grade>=60)))

The last is a Boolean function.


--ron

 

[Gord Dibben]

Kevin

From Peo Sjoblom....
=IF(A1="","",VLOOKUP(A1,{0,"F";0.6,"D";0.7,"C";0.8,"B";0.9,"A"},2))

From Jason Morin......
=LOOKUP(A1*100,{0,60,70,80,90},{"F","D","C","B","A"})

Adjust ranges of numbers to letters to suit.

Gord Dibben Excel MVP