Excel Formula

[AR1Redneck]

Hi all, i am doing this project for my school work. Its a grading system project. Ok let me see if i can explain it good enough for you to understand.

OK i have a spreadsheet with all the grades on it, with all of them weighteed properly. The percentaage comes out perfect. What i am looking to do is, under the percentage, put the letter grade. For example, A, A-, B+, B, B-, and etc... OK i have the intervals that the percentage has to be in order to be a A or B and so on, but how can i formulate that into a formula so that when i put the grades in, i can get the letter grade as well as the percentage.

I have the formula for the percentage all complete, just need the one for the letter grade.

I saw somewhere there is a formula that contains an interval or something that if i say C19=83-90, then it would out a B in the cell. Anyone know how that works? It didnt work if i just put that into the cell. Thanks

Drew

 

[Anne Troy]

Hey UR1:

Go download MyVlookup at:

http://www.theofficeexperts.com/downloads.htm#ExcelDownloads

I think you'll find it's just what the teacher ordered.

 

[Anne Troy]

Hey. I closed this up, then clicked another Q here, and found this:

http://forums.techguy.org/t111970/s355295e73026659875fedef073f808cb.html

I didn't check out the file. I'll let you do that.

 

[AR1Redneck]

i Downloaded the one off of your website, im reading it now, but not really grasping it, VLOOKUP? what does that stand for? If gives the formula =VLOOKUP(A2,MyLookup,2,FALSE)

A2= the cell that my percent is in correct?
MyLookUp= the range? this is where i get lost

not sure about the rest, lol sorry

Drew

 

[Anne Troy]

The instructions are all there. Did you look at each worksheet?

Make a small table on another worksheet, 2 columns:

Col A
Percent (start % of Grade)
100
90
80
70

Col B
Grade (corresponds to % above)
A
B
C
D

Select the cells that contain 100 through 70 AND A through D
(should be cells A2 through D4 for my example).

After selecting, hit Insert-Name-Define

Type: Mylookup or something like that, and hit enter.

You've named your range.

Okay, on Sheet 1 you've got names in col A, % in Col B and you want the grade to appear in col C. Your headings are in row 1.

In cell C2, you'll put:

=VLOOKUP(B2,MyLookup,2)

B2 is where your % is, right? It'll go to the range called mylookup and it'll go to the 2nd column which is the grades, and return that value.

In your case, you don't want the FALSE argument.

If you can't get it to work, email your file: Anne@TheOfficeExperts.com

 

[AR1Redneck]

you have mail, sorry

 

[Anne Troy]

Don't apologize for taking someone up on something they offered.



There is no shame in learning, right??
Oh GAWD! I hope not!!

 

[RandyG]

Dreamboat's suggestion is probably exactly what you need, but there is another, more limited, choice. It's called nested IF statements

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

The way this works is it checks the Cell (A1) and sees if it fulfills certain criteria. If it does fulfille the criteria, then it will display a result, and if it does not fulfill the criteria, it will check it against the next set of criteria.

so, let's say the number in Cell A1 is 90, then by the formula I have above, an A would be displayed in whichever Cell I input that formula. Here's how it works:

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

1. =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"") This will check to see if there is any value in the cell at all, and if there is, it will go to the validation stage. If there isn't, then it will just stay blank
2. =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"") This then checks to see if the value of A1 is less then the number I have determined to be a failure (60 in this example). If the number were less than this value, then F would be displayed, otherwise it goes to the next IF statement
3. =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"") IF the number were less than 70, it would display a D, but since it is not, it goes to the next IF statement
4. =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"") If the number was less than 80, it would display a C, otherwise it will go to the next IF statement
5. =IF(A1>0,IF(A1<60,"F",IF(A1<70,"D",IF(A1<80,"C",IF(A1<90,"B","A")))),"") If the number were lower than 90, then it would display B, otherwise it will display an A
   [/list=1]
   
   
   Do you get how it works??? It is a bit bulky, but is great for small computations. The major limiting factor is that is gets confusing when dealing with many IF statemenst, but the most limiting factor is that you can only nestle 7 IF statements together in One total statement. That means if you wanted a more specified grading scale, for instance having +'s and -'s added to the grade, you would not be able to do it with the IF statements, but would have to do the vlookup that Dreamboat has helped you with.
   
   
   I have only provided you with this as an example of alternatives, and also because you are learning. Hopefully I have explained myself well enough. The IF statement is perfect for limited functions.

 

[phil-key]

You could also try the Excel "Choose" function. Which is sort of like a programming Case statement.

CHOOSE(index_num,value1,value2,…)
Index_num specifies which value argument is selected. Index_num must be a number between 1 and 29, or a formula or reference to a cell containing a number between 1 and 29.

· If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
· If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
· If index_num is a fraction, it is truncated to the lowest integer before being used.

Value1, value2, ¼ are 1 to 29 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or text.

If your letter grades fall into this range structure:
a=90-100
b=80-89
c=70-79
d=60-69
f<65

You could use the following formula:
=CHOOSE(TRUNC(A2/10),"F","F","F","F","F","D","C","B","A","A")

In other words, divide the grade by 10, truncate the decimal and use that as the index to the letter grade.

 

[Anne Troy]

I apologize profusely.

I did get a file from the Redneck and showed him how to do the vlookup and, as far as I know, that worked well.

 

[RandyG]

don't apologise, unless you're being a smartarse!!


I knew as soon as I saw he emailed you that you'd sort it, but I only wanted him to know of alternatives.

 

[Anne Troy]

Dork.

When are you inviting me over??

 

[RandyG]

You're always welcome, but with that attitude, it'll be a spanking you'll be getting . . . but without all the fun connotations!!


At least we'll be able to get a nice Irish Coffee into you afterwards, and a proper one at that! None of that horrible whipped cream garbage, especially not the stuff from a can!!!

 

[Anne Troy]

My mom was a "cook", Randy.

I only make fresh whipped cream.

 

[AR1Redneck]

lmao @ dreamboat and randy...

Yes i did get it to work right, thanks for the help dreamboat. And also thanks to the others who offered suggestions, i will try them and see if they work also. Thanks

Drew