EXCELL Question 2

[JJOHNS]

I want to determine a letter grade from a percentage in Excel. right now I can get an "A" if the percentage is .90 or higher, but I can't get it to choose between all five grades.

What I have so far: IF(L12>=.9,"A","&quot

How do I get it to choose A, B, C, D, or F, based on the percentage?

 

[JVFriederick]

Construct a table with the grade ranges
A B C
.50 F .77
.70 D
.80 C
.90 B
1.0 A

Enter the grade of .77 in C1, then enter the formula in C2 :
=VLOOKUP(C1,A1:B5,2,TRUE)

The result should be "D"

The key here is the TRUE parameter in the VLOOKUP function, which uses the largest value that is less than or equal to lookup value.

 

[DaleWatson123321]

JV's solution is the right one, BUT, in double-checking his quick response, there are TWO potential problems.

1) If a student's score is LESS than .50, then the formula will return "#N/A". One solution is to use "0" in place of the .50, causing ANY score below .70 to be graded as "F".

2) If "JJOHNS" intends to copy the formula - i.e. for application to a number of rows for other students' scores - then there will be a NEED to make the reference to the table "ABSOLUTE". Otherwise, the cell reference to the table range will CHANGE when the formula is copied.

Making the reference to the table "ABSOLUTE" can be done by placing the cursor on the table's cell references and using the "F4" function key. This will insert "$" characters which causes the cell references to remain ABSOLUTE.

However the BEST method of creating an "absolute" reference in a formula, is to create a "Range Name". After creating an arbitrary Range Name ("grade" would be appropriate in this case), Excel will simply accept the range name. AND, when the formula is copied, the range name STAYS AS IS - i.e. it remains "ABSOLUTE".

For those not aware of the "easy" method of creating a "Range Name", use these steps:

1) Highlight the range you want to name,

2) Hold down the "Control" key, and hit the "F3" key,

3) Type in the (arbitrary) name you wish to use, and hit the "Enter" key.

Hope this provides a valued "assist" to JV's solution.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JJOHNS]

Thanks for the help. I put the formula given into my spreadsheet and it returned the right answer, the first time. When I changed the percentage from 97 to 67, the letter stayed A.

 

[JJOHNS]

Go ahead, laugh. I forgot to put in the decimal. the formula works fine. thanks a lot.

 

[JVFriederick]

I must have assumed JJOHNS was the instructor . . . .
and wouldn't have any students with a score that low . . .

Thanks for the help Dale !