# Excel Formula

Discussion in 'Business Applications' started by AR1Redneck, Jan 7, 2003.

Not open for further replies.

Joined:
Dec 13, 2002
Messages:
206
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

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
3. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne

Joined:
Dec 13, 2002
Messages:
206
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

5. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
The instructions are all there. Did you look at each worksheet?

Make a small table on another worksheet, 2 columns:

Col A
100
90
80
70

Col B
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.

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: [email protected]

Joined:
Dec 13, 2002
Messages:
206
you have mail, sorry

7. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
Don't apologize for taking someone up on something they offered.

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

8. ### RandyG

Joined:
Jun 26, 2000
Messages:
7,762
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.

9. ### phil-key

Joined:
May 3, 2000
Messages:
24
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.

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.

10. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
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.

11. ### RandyG

Joined:
Jun 26, 2000
Messages:
7,762
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.

12. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
Dork.

When are you inviting me over??

13. ### RandyG

Joined:
Jun 26, 2000
Messages:
7,762
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!!!

14. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
My mom was a "cook", Randy.

I only make fresh whipped cream.

Joined:
Dec 13, 2002
Messages:
206
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

As Seen On