Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

VLOOKUP assumes your table is in ascending order, not descending. Change
your table to:

0.00 F
0.50 D
0.65 C
0.70 B
0.85 A

And it should work.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Neoseraph" wrote in message
...
Hi, I need help to setup the VLOOKUP function for an assignment. Everytime
I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

Hmmm, yeah, now that gave me "A" for grades that are higher then 0.85 but
then when I try to drag the formula down into the other cells, they all gave
me "A" too even when the grade is 5% (which is suppose to be "F"), so what's
wrong?

"Fred Smith" wrote:

VLOOKUP assumes your table is in ascending order, not descending. Change
your table to:

0.00 F
0.50 D
0.65 C
0.70 B
0.85 A

And it should work.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Neoseraph" wrote in message
...
Hi, I need help to setup the VLOOKUP function for an assignment. Everytime
I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

How do I setup the True or False as range lookup?


"Bruno Vermeersch" wrote:

You omitted True or False as Range_Lookup.
This means that True is assumed.
So : your values must be in ascending order.

Try this in the cells I9:J13
0% F
50% D
65% C
70% B
85% A

It should be working now,
Bruno


"Neoseraph" wrote in message
...
Hi, I need help to setup the VLOOKUP function for an assignment. Everytime
I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

Ok, here's what I did for the Range_lookup part:

I put in 0.00 and it give me false instead of true if I put 0.85, but then
that gave me #NA again. So what's wrong?

"Neoseraph" wrote:

How do I setup the True or False as range lookup?


"Bruno Vermeersch" wrote:

You omitted True or False as Range_Lookup.
This means that True is assumed.
So : your values must be in ascending order.

Try this in the cells I9:J13
0% F
50% D
65% C
70% B
85% A

It should be working now,
Bruno


"Neoseraph" wrote in message
...
Hi, I need help to setup the VLOOKUP function for an assignment. Everytime
I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

Another method of grading as you desire.

Enter this formula in B1. You don't need a separate table, the table is in
the formula.

=LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"})

Enter a number in A1


Gord Dibben Excel MVP


On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph
wrote:

Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

Ok, when I tried your method, it gives me #REF! either by entering a number
in A1 or the cell A1 itself, please help me solve the problem...

"Gord Dibben" wrote:

Another method of grading as you desire.

Enter this formula in B1. You don't need a separate table, the table is in
the formula.

=LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"})

Enter a number in A1


Gord Dibben Excel MVP


On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph
wrote:

Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

If you get a REF error with that formula you must have either deleted
something linked to A1 or used a non existent reference. Try the formula in
a new workbook and put in a value in A1

--
Regards,

Peo Sjoblom


"Neoseraph" wrote in message
...
Ok, when I tried your method, it gives me #REF! either by entering a
number
in A1 or the cell A1 itself, please help me solve the problem...

"Gord Dibben" wrote:

Another method of grading as you desire.

Enter this formula in B1. You don't need a separate table, the table is
in
the formula.

=LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"})

Enter a number in A1


Gord Dibben Excel MVP


On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph
wrote:

Hi, I need help to setup the VLOOKUP function for an assignment.
Everytime I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of
students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

Sorry, I tried to open a new workbook and it still doesn't change anything,
this is what the formula looks like with a cell:

=VLOOKUP(D8,{0,"F";0.5,"D";0.65,"C";0.7,"B";0.85," A"},2)

and this is what the formula looks like with a new value:

=VLOOKUP(45,{0,"F";0.5,"D";0.65,"C";0.7,"B";0.85," A"},2,0.85)

"Peo Sjoblom" wrote:

If you get a REF error with that formula you must have either deleted
something linked to A1 or used a non existent reference. Try the formula in
a new workbook and put in a value in A1

--
Regards,

Peo Sjoblom


"Neoseraph" wrote in message
...
Ok, when I tried your method, it gives me #REF! either by entering a
number
in A1 or the cell A1 itself, please help me solve the problem...

"Gord Dibben" wrote:

Another method of grading as you desire.

Enter this formula in B1. You don't need a separate table, the table is
in
the formula.

=LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"})

Enter a number in A1


Gord Dibben Excel MVP


On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph
wrote:

Hi, I need help to setup the VLOOKUP function for an assignment.
Everytime I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of
students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

"Neoseraph" wrote:

Sorry, I tried to open a new workbook and it still doesn't change anything,
this is what the formula looks like with a cell:

=VLOOKUP(D8,{0.00,0.50,0.65,0.70,0.85},{"F","D","C ","B","A"})

and this is what the formula looks like with a new value:

=VLOOKUP(45,{0.00,0.50,0.65,0.70,0.85},{"F","D","C ","B","A"})

"Peo Sjoblom" wrote:

If you get a REF error with that formula you must have either deleted
something linked to A1 or used a non existent reference. Try the formula in
a new workbook and put in a value in A1

--
Regards,

Peo Sjoblom


"Neoseraph" wrote in message
...
Ok, when I tried your method, it gives me #REF! either by entering a
number
in A1 or the cell A1 itself, please help me solve the problem...

"Gord Dibben" wrote:

Another method of grading as you desire.

Enter this formula in B1. You don't need a separate table, the table is
in
the formula.

=LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"})

Enter a number in A1


Gord Dibben Excel MVP


On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph
wrote:

Hi, I need help to setup the VLOOKUP function for an assignment.
Everytime I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of
students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

Is D8 formatted to Percentage?

Entering 45 if not formatted to % will always return A because 45 is much
higher than .85


Gord Dibben Excel MVP

On Sun, 3 Apr 2005 11:07:02 -0700, Neoseraph
wrote:



"Neoseraph" wrote:

Sorry, I tried to open a new workbook and it still doesn't change anything,
this is what the formula looks like with a cell:

=VLOOKUP(D8,{0.00,0.50,0.65,0.70,0.85},{"F","D","C ","B","A"})

and this is what the formula looks like with a new value:

=VLOOKUP(45,{0.00,0.50,0.65,0.70,0.85},{"F","D","C ","B","A"})

"Peo Sjoblom" wrote:

If you get a REF error with that formula you must have either deleted
something linked to A1 or used a non existent reference. Try the formula in
a new workbook and put in a value in A1

--
Regards,

Peo Sjoblom


"Neoseraph" wrote in message
...
Ok, when I tried your method, it gives me #REF! either by entering a
number
in A1 or the cell A1 itself, please help me solve the problem...

"Gord Dibben" wrote:

Another method of grading as you desire.

Enter this formula in B1. You don't need a separate table, the table is
in
the formula.

=LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"})

Enter a number in A1


Gord Dibben Excel MVP


On Sun, 3 Apr 2005 08:15:03 -0700, Neoseraph
wrote:

Hi, I need help to setup the VLOOKUP function for an assignment.
Everytime I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of
students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

Make your 'table-array' references absolute. Instead of
=VLOOKUP(D8,I9:J13,2), use =VLOOKUP(D8,$I$9:$J$13,2). Then when you drag the
formula, you'll still be reference the proper lookup table.



"Neoseraph" wrote:

Hmmm, yeah, now that gave me "A" for grades that are higher then 0.85 but
then when I try to drag the formula down into the other cells, they all gave
me "A" too even when the grade is 5% (which is suppose to be "F"), so what's
wrong?

"Fred Smith" wrote:

VLOOKUP assumes your table is in ascending order, not descending. Change
your table to:

0.00 F
0.50 D
0.65 C
0.70 B
0.85 A

And it should work.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Neoseraph" wrote in message
...
Hi, I need help to setup the VLOOKUP function for an assignment. Everytime
I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???

Set your Grade Table up as follows

0 F
50% D
65% C
70% B
85% A

Do Insert Name Define, and name this range GRADES

Then, assuming your GradePercentage is in A1, use this formula to look up
the grade

=VLOOKUP(A1,GRADES,2,TRUE)


Vaya con Dios,
Chuck, CABGx3


"Neoseraph" wrote in message
...
Hi, I need help to setup the VLOOKUP function for an assignment. Everytime
I
try to do it, it says, #NA.

I need to use VLOOKUP to put in the letter grade for a list of students's
grades in % on their midterms.

The average % is in cells D8:D18.

The lookup table I made (in cells I9:J13) is as followed:

85% and above A
70% and above B
65% and above C
50% and above D
Below 50% F

This is what I tried for VLOOKUP but got #N/A:

lookup value: D8
table array: I9;J13
col_index_num: 2

so the final formula looks like this: =VLOOKUP(D8,I9:J13,2)
and that gave me #N/A.

Can anybody help please???