Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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 "Kimmer" wrote: 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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 On Fri, 4 Apr 2008 13:26:00 -0700, Kimmer wrote: 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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. -- Too old to be in college "Dave" wrote: 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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
the first time I did it I only put the E2 in there. I will try changing the
formula again. -- Too old to be in college "Tom Hutchins" wrote: 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 "Kimmer" wrote: 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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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 On Fri, 4 Apr 2008 14:08:00 -0700 (PDT), Dave wrote: 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. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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. -- Too old to be in college "Tom Hutchins" wrote: 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 "Kimmer" wrote: 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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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. -- Too old to be in college "Gord Dibben" wrote: 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 On Fri, 4 Apr 2008 13:26:00 -0700, Kimmer wrote: 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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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 "Tom Hutchins" wrote: 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 "Kimmer" wrote: 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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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 On Fri, 4 Apr 2008 14:35:04 -0700, Kimmer wrote: 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. |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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. -- Too old to be in college "Gord Dibben" wrote: 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 On Fri, 4 Apr 2008 14:35:04 -0700, Kimmer wrote: 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. |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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" wrote: 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 "Tom Hutchins" wrote: 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 "Kimmer" wrote: 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 |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup
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. -- Too old to be in college "Tom Hutchins" wrote: 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" wrote: 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 "Tom Hutchins" wrote: 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 "Kimmer" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |