Vlookup

[Kimmer]

I am trying to do my homework again. I have done everything I can to get
this to work and I keep getting error messages.

I am trying to get the letter grade on 3 worksheets. I have a forth
worksheet that has the criteria for the letter grades. I have a list of
grade scores and I need to get the letter on it. I have put in this formula
and it shows the first letter grade but shows errors in the rest.
VLOOKUP(E2:E16,'Grading Criteria'!A2:B6,2) I am not sure what I am doing
wrong. I copy try to highlight down the column and then get like 2 more of
the 16 students grades as B the rest show an error #NA. What am I doing
wrong?
Cell A2 Cell B2 A6 B6
-- 0 F 90 A
A3 B3
50 D
A4 B4
60 C
A5 B5
75 B
Too old to be in college

 

[Dave]

Hi Kimmer,
Never too old.
Your VLOOKUP statements are separarted by commas. I use XL2000, and I
need to use semicolons, or else I get error messages. Is this it, or
is it just a version thing?
Did you use the function input thingywhen you created the formula? It
helps remove the possibility of spelling errors and typo's.
Also, your VLOOKUP formula should really have a 4th statement after
the 2, which in your case should be ;FALSE.

Regards - Dave.

 

[Tom Hutchins]

I think you need to make two changes to your formula:
1. Only refer to a single cell in column E (for example, E2). When you copy
the formula down the column for the other cells, the reference will change
automatically.
2. Your reference to the range on the Grading Criteria sheet needs to be
made absolute, so it WON'T change as you copy the formula down. To make a
column or row address absolute, put a $ in front of it.

So, your revised formula would look like this for row 2:

=VLOOKUP(E2,'Grading Criteria'!$A$2:$B$6,2)

and copy it down through row 16. Hope this helps,

Hutch

 

[Gord Dibben]

Make your Table absolute either by naming it or by adding the $ signs.

And lose the :E16 You can't have a range as a lookup value.

VLOOKUP(E2,'Grading Criteria'!$A$2:$B$6,2)

You may also want to add the FALSE argument so exact data or #N/A is returned so
you don't get closest match and no need for sorting data in ascending order.


Gord Dibben MS Excel MVP

 

[Kimmer]

I have 2003. This is for a spreadsheet software class. I did go to functions
and go through the functions arguments. I still get error if I put True or
False. This is an online class and our teacher is of no help. He doesnt
respond to e-mails or help.

 

[Kimmer]

the first time I did it I only put the E2 in there. I will try changing the
formula again.

 

[Gord Dibben]

Dave

Semi-colons versus commas are a Windows Regional Settings thing.

North America uses the comma exclusively. Move to Europe and you will find the
semi-colons in wide use.


Gord Dibben MS Excel MVP

 

[Kimmer]

that worked. Thank you. In the book it does show the $ before A and 2 and B
and 6 but for some reason when I was hightlighting the cells in the workbook
it did not put them in the table array that way. Thank you. I may have more
questions as I continue on.

 

[Kimmer]

Changing to the $ signs did work. I originally had only the E2 in there but
I thought that was the problem so I changed it. I also had highlighted the
A2:B6 and it does show in my text about the $ signs but it did not do it when
they were put into table array. It works now. Thank you very much.

 

[Kimmer]

Now I have one more question. I moved on and have gone back through book and
the next thing does not work. I have added this to the grading criteria
worksheet.
A9 B9
-Exam Type Weight
A10 B10
Exam1 25%
A11 B11
Exam2 25%
A12 B12
Final 50%
They want you to modify the formula in column E of each of the sections
worksheets so the weight applied is computing the scores based on data
obtained from the range B10:B12 in the grading criteria worksheet. They want
you to change the exam1 from 25 to 15 and the final from 50 to 60. Which I
did manually. Then I made sure the sections worksheets were grouped. I put
the = in and then clicked on the grading criteria worksheet and highlighted
the B10:B12 like it claims to do in our book, and then just hit the enter
button on formula toolbar. Well, now I get a #Value error. Original formula
was put in manually and is =SUM(B2*0.25+C2*0.25+D2*0.50). Not sure what I am
doing wrong. It worked when I did the tutorial but it was only one cell and
not 3 of them.
Too old to be in college

 

[Gord Dibben]

Good to hear.

What's this "too old to be in College" stuff?

If I had the financial resources I would go back full-time at my age of 65.

As it is I take local Community College courses when I can.


Gord

 

[Kimmer]

thanks for your help. Just stressful with working full time and helping my
husband with his business and trying to go to school and then not
understanding how to do stuff. I have another post I put on about links and
formulas. I got another snag in my homework. My book has not been very
helpful. Worked for what you do in the tutorial but not when you do the
problems. Thanks for your help.

 

[Tom Hutchins]

I'm not sure I understand what you are trying to do. Your earlier formula had
the percentages hard-coded in the formula, and now you are supposed to get
the new percentages from B10:B12? Just replace the first 0.25 in your formula
with $B$10, the next 0.25 with $B$11, and 0.50 with $B$12, like this:

=SUM(B2*$B$10,C2*$B$11,D2*$B$12) or this

=B2*$B$10+C2*$B$11+D2*$B$12

Your original SUM formula had + operators within the SUM function arguments.
That will work in this case, but you are in effect adding the values using
the + signs, then taking the SUM of the total.

Learning new things keeps you young.

Hutch

 

[Kimmer]

I will try this. Thanks. The way it shows in our book you are modifying a
vlookup formula and this column was not originally that way. I will try it
and see if it works. Thank you so much for your help.