VLookup

[Elizabeth]

Hello everyone. I need help! What I need is a VLookup formula to insert a
letter grade into the last column on the second worksheet. Thank you!
Elizabeth

The following is on worksheet 1

Column A Col B
0 to 49 F
50 to 59 D
60 to 74 C
75 to 89 B
90 to 100 A

The following is on worksheet 2

Student ID Exam 1 Exam 2 Final Overall Grade
318-84-6039 100 93 79 87.75
332-03-6854 99 90 74 84.25
341-38-6902 63 51 43 50.00
362-80-6830 65 91 81 79.50
351-25-6606 74 65 58 63.75
303-86-6698 74 63 63 65.75
360-99-6115 63 50 43 49.75
342-45-6149 84 72 65 71.50
360-49-6615 78 65 58 64.75
331-38-6683 88 78 77 80.00
337-55-6535 72 64 62 65.00
300-60-6949 100 89 85 89.75
373-46-6412 99 88 84 88.75
397-44-6180 98 84 84 87.50
398-56-6736 100 92 86 91.00

 

[T. Valko]

Try this:

=LOOKUP(E2,{0,1,50,60,75,90},{"","F","D","C","B","A"})

Biff

 

[Roger Govier]

Hi Elizabeth

On Sheet1, in column A you only need the first of the figures, not the
range
0 F
50 D
60 C
75 B
90 A

Then on Sheet2 in cell F2
=VLOOKUP(E2,Sheet1!$A$1:$B$5,2)

However, I can't see how you have arrived at the values in column E.
They are not arithmetic average of columns B,C and D so I assume each
test must have been given a different weighting.

 

[Jerry W. Lewis]

If you assume that Exam 1 and Exam 2 get the same weight, then the final
counts double.

Jerry

 

[Roger Govier]

Well spotted Jerry!
Many thanks.

 

[Elizabeth]

Hi Roger
I used :
Percentage applied to final score 1st exam 25%, 2nd exam 25%, and final exam
is worth 50%.
Elizabeth

 

[Elizabeth]

Hi Biff
that worked. thank you. however, the assignment I'm working on said to use
"Vlookup" specifically. Is there a difference btwn vlookup & the formula you
gave me?
Elizabeth

 

[T. Valko]

Slight difference:

=VLOOKUP(E2,{0,"";1,"F";50,"D";60,"C";75,"B";90,"A"},2)

Since you have only 5 variables, it's not really necessary to create a
separate lookup table. You can code that many variables in the formula
itself.

If this is an assignment (homework? school work?) and you are required to do
specifically as directed then use Roger's suggestion. Or, if you might get
extra credit (brownie points for thinking outside the box!!) use both mine
and Roger's suggestions!

Biff

 

[Roger Govier]

Hi Elizabeth

Thanks for posting back with that.
As you may have noticed Jerry (being a very bright mathematician) had
posted with that same observation.