Any excel geniuses out there that can help me with this?

[paula777]

What =vlookup code I would use in excel to assign a letter grade to the following points (student points in some class)(see below info). In other words, 50 points equals an F and 60 equals a C. I need to know what vlookup code to use so excel assigns the grade to the points automatically (obviously it's easy to do it by hand but the test requires you have excel do it). Any excel wizzes out there? (I have an interview with a mandatory excel test on Wednesday and I WANT the job so I want to do well on the test). Thanks for helping if you can.


Student Average per student / 100 Letter Grade
Wailers 50
Peter Tosh 61.75
Bob Marley 67.25
Madness 59.75
Specials 64.75
UB40 73.25

Points Letter
45 F
50 F
55 D
60 C
65 B
70 A
75 A

[democrat_patriot]

1. Use an if than in your cell

Not sure of the actual syntax

IF C45 is BETWEEN 45 AND 50 THEN "F" ELSE

IF C45 is BETWEEN 51 AND 59 THEN "D" ELSE

etc...

I'm not a programmer, so I may be way off base...

[Gman]

2. you need to do an if statement with an "and" statement

=if(and(cell with number grade>0,cell with number grade<=55),"F",if(and(cell with numbergrade>55,cell with number grade<=60),"D",if.........etc.)

watch to make sure all your parenthesis are closed.

that's a tough one... you can't do a lookup.

and RTFM or RTHF.

[gandalf]

3. Lookup does not work here

you should write a small vba program...

in order to use vlookup, you must have an expression in your vlookup command that can be found in the points-grade definition. But 61.75, for instance, cannot be found in your points-letter matrix. So vlookup does not work.

[BrainRants]

4. Not an Ecel expert (know enough to be dangerous)

But I am a Google Geek, and putting the two together I found this:

http://spreadsheets.about.com/cs/excelfunction1/a/iffunction.htm

[racaulk]

5. I don't think a VLOOKUP function would work...

It's dependent on the exact values you're looking up being present in the lookup table. For example, Peter Tosh made a 61.75, but since that number score isn't in your lookup table, a VLOOKUP function won't return the letter score value you're looking for.

I think an IF function would be more appropriate. Assume Wailers' score of "50" is in cell B2. Try this formula:

=IF(B2>67.5,"A",IF(B2>62.5,"B",IF(B2>57.5,"C",IF(B2>52.5,"D","F"))))

I'm assuming the midpoints based on the table provided. For example, a 70 is an A and a 65 is a B, so I'm assuming a 67.5 is the cutoff between the two grades. What a weird grading scale that is...

I hope this works for you. Let me know if it doesn't. :)

[paula777]

7. Wow, well that came back with a straight 'F' - This was actually

a test question that was much longer but I edited it to fit on the DU board. It's a very specific V lookup question that they say is standard on excel tests. I'm starting to wonder if this job just isn't for me if I don't know this stuff, Your formula, for example, is so far over my head (I just cut and pasted it in the worksheet to see if it would work. Thank you for your help.

[MindPilot]

6. Not an Excel wizard, but I've played one in the classroom

My first thought--like the other poster--would've been to do a series of nested IF statements, but that can get pretty complicated for a whole set of letter grades.

Since you required to do this with the vlookup function, check here on the Microsoft site, it will show you how to lay out the data.

http://office.microsoft.com/en-us/assistance/HP052093351033.aspx

Hope that helps and good luck!!!

[paula777]

9. I saw this one, here is specifically what it is asking me for on the sheet

if you put in =vlookup( then it starts prompting you. It says to put in: lookup_value, table, array, col_index_num,). I don't know what the look up value is, is it the grade or the number assigned to the grade? What is the table? What is the array? I don't know. Thanks for the link and the other links here - I am desperatly reading through them to see if I can find out. THANKS

[MindPilot]

13. Again I'm no expert, but here's the way I understand this

the lookup value is the number grade assigned to each student, the table array is the data that connects a letter grade to the number grade. So your table array would be the two columns of data:
45 F
50 F
55 D
60 C
65 B
70 A
75 A
The column index is the number of the column containing the letter grade--the column which contains the answer. In this case you'll need to set the range to TRUE so the function can find non-exact matches.

[RamboLiberal]

8. This link might help - quick look seems that it may be just

what you're looking for.

http://www.utexas.edu/its/training/handouts/excelgrade/

Lookup Tables
The IF( ) function is very useful, but it is limited to either TRUE or FALSE outcomes. In many worksheets, you might want to create a function that handles multiple outcomes. Excel's VLOOKUP( ) function is ideally suited for this sort of calculation.

With the VLOOKUP( ) function (short for vertical lookup) you can specify lookup values for different outcomes. For example, if you have a list of numeric averages in a worksheet, you can create a formula that assigns letter grades based on a student's numeric score (e.g. a score of 76 would be a C).

To use a VLOOKUP( ) function, you must first create a lookup table with a range of values. This lookup table is similar in concept to a tax table. When using a tax table, find your income in the first column and then read across to the column that applies to you, single, married etc. A sample lookup table to handle letter grades appears in cells E2:G6 below. In this case, 0-59 is an F, 59-69 is a D, and score above 89 is an A.

see the link I posted for the table. BTW, DU has a Computer Help and Support Forum.

[TruthIsAll]

10. This is your answer using Excel VLOOKUP..

The range f15.g21 contains the rules to map a numeric grade to
an alphabetic one.

Col H contains the numeric grades,
Col I uses vlookup, as shown in Vol J.


F	G	H	I	J

45	F	48	F	=VLOOKUP(48,$F$15:$G$21,2)
50	F	51	F	=VLOOKUP(51,$F$15:$G$21,2)
55	D	53	F	=VLOOKUP(53,$F$15:$G$21,2)
60	C	58	D	=VLOOKUP(58,$F$15:$G$21,2)
65	B	67	B	=VLOOKUP(67,$F$15:$G$21,2)
70	A	71	A	=VLOOKUP(71,$F$15:$G$21,2)
75	A	73	A	=VLOOKUP(73,$F$15:$G$21,2)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)


This is the formula which should be copied down in col I
=VLOOKUP($H15,$F$15:$G$21,2)
=VLOOKUP($H16,$F$15:$G$21,2)
=VLOOKUP($H17,$F$15:$G$21,2)
=VLOOKUP($H18,$F$15:$G$21,2)
=VLOOKUP($H19,$F$15:$G$21,2)
=VLOOKUP($H20,$F$15:$G$21,2)
=VLOOKUP($H21,$F$15:$G$21,2)

[paula777]

12. You use dollar signs????? Okay, I'll try that. Thanks

[TruthIsAll]

14. Using $ to keep the row or column (or both) fixed when you copy a formula

This is the formula which should be copied down in col I
=VLOOKUP($H15,$F$15:$G$21,2)
=VLOOKUP($H16,$F$15:$G$21,2)
=VLOOKUP($H17,$F$15:$G$21,2)
=VLOOKUP($H18,$F$15:$G$21,2)
=VLOOKUP($H19,$F$15:$G$21,2)
=VLOOKUP($H20,$F$15:$G$21,2)
=VLOOKUP($H21,$F$15:$G$21,2)


Note that we want ONLY the row number to change when we copy
the formula (from $H15 to $H21)

The 2 in the last column refers to the second column in the
vlookup range. 

[paula777]

11. Thanks for everyone's help - here is the link to the test I am trying to

figure out. It is more specific than my post. Any help is appreciated.

http://www.earlham.edu/~markp/mgmt_2003/assignments/quiz/excel_test_03.xls