How to use VLookup with a range of numbers?
Hi I'm trying to solve this problem I have and could use some help, I was
wondering if its possible to use VLookup to check through a range of data in
one cell, ex: 89-100 or 71-74. But I'm not sure if there is a way to make
excel understand FROM 89 TO 100 0r 71 TO 74. (Yes I've just started learning
excel on my own)
This is the problem I'm trying to solve:
To calculate the data in the 20 Point Scale column, enter a formula that
converts each students Final Percentage to a number grade on the 20 Point
Scale as outlined below:-
80% and above = 1
75-79% = 2
71-74% = 3
70% = 4
67-69% = 5
64-66% = 6
61-63% = 7
60% = 8
57-59% = 9
54-56% = 10
51-53% = 11
50% = 12
47-49% = 13
44-46% = 14
41-43% = 15
40% = 16
35-39% = 17
30-34% = 18
20-29% = 19
0-19% = 20
The formula used to calculate the 20 Point Scale grades should accomplish
the following:-
If a student has 0 for their Final Percentage, they should automatically
receive an X in the 20 Point Scale column (use an IF function to accomplish
this).
Otherwise, students grades are calculated using the scale in the table
above (use the VLOOKUP function to accomplish this).
The formula must use cell references. You must not use numbers or text
inside any of the formulas or functions on the worksheet.
Create additional tables in the worksheet to accomplish this.
Hint: Create a table for the 20-point scale in a separate area of the
worksheet and use the VLOOKUP function to convert the final percentage to the
correct number on the 20-point scale. You will need to set up the correct
breakpoints in this table in order for the VLOOKUP function to work.
Would love some help or a helpful link,
Thanks
|