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

How to use VLookup with a range of numbers?
 

Create a lookup table like this:

A B
0 20
20 19
30 18
35 17
and so on...

then use VLOOKUP function


"Snake" wrote:

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

How to use VLookup with a range of numbers?
 

Would love some help or a helpful link

http://contextures.com/xlFunctions02.html

--
Biff
Microsoft Excel MVP


"Snake" wrote in message
...
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 student's 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

How to use VLookup with a range of numbers?
 

Thanks folks problem solved, really appreciated the help.

I'm posting a 2 more problems i got would really appreciate ur help

Thanks very much