need if-then formula-If a number is > /< something, then place a .

[Guest]

i need a formula to place in my spreadsheet that provides the following:
If a number is > or < a value, then place an alpha character in the cell.
I am a teacher and am trying to have the excel spreadsheet put the letter
grade in the cell where the total number of points earned is (because we
don't use the number of points, but letter grades).

 

[N Harkawat]

=LOOKUP(A1,{0,50,60,70,80,90;"F","E","D","C","B","A"})
Adjust accordingly assuming the scoiresa are on cell A1

 

[Guest]

You would probably be better off using a VLOOKUP function instead of having
to nest several IFs. You first need a "Table" of point/letter-grade data
somewhere in your workbook, such as:
F G
1 Points Letters
2 0 F
3 60 D
4 70 C
5 80 B
6 90 A

Your formula adjacent to each student name & Point grade would be:

A B C
1 Johnny 77 =VLOOKUP(B1,$F$2:$G$6,2) Resulting in a letter grade of C
2 Suzie 82 =VLOOKUP(B2,$F$2:$G$6,2) Resulting in a letter grade
of B
3 Tommy 97 =VLOOKUP(B3,$F$2:$G$6,2) Resulting in a letter grade of A
4 Sleepy 58 =VLOOKUP(B4,$F$2:$G$6,2) Resulting in a letter grade of F

The formula scans the first column of the table [F2 to F6] to locate the
point value next to the student name [Column B], but will not go higher. Once
it determines the correct row it looks to the second column [2], and returns
the value it finds there [G2 to G6]. The "$"s are used to make the cell
references for the table _absolute_ so you can just write the formula once
and copy it down the column without it changing.

BTW- if you later decide to grade on a "curve, you need only change the
Point values in Column F of the table. Each affected student's letter grade
will update automatically as appropriate... you don't need to mess with
editing the formulas.

HTH |:>)

 

[Gord Dibben]

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"})

Adjust the ranges and letters to suit.


Gord Dibben Excel MVP