| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup
Hello everyone. I need help! What I need is a VLookup formula to insert a
letter grade into the last column on the second worksheet. Thank you! Elizabeth The following is on worksheet 1 Column A Col B 0 to 49 F 50 to 59 D 60 to 74 C 75 to 89 B 90 to 100 A The following is on worksheet 2 Student ID Exam 1 Exam 2 Final Overall Grade 318-84-6039 100 93 79 87.75 332-03-6854 99 90 74 84.25 341-38-6902 63 51 43 50.00 362-80-6830 65 91 81 79.50 351-25-6606 74 65 58 63.75 303-86-6698 74 63 63 65.75 360-99-6115 63 50 43 49.75 342-45-6149 84 72 65 71.50 360-49-6615 78 65 58 64.75 331-38-6683 88 78 77 80.00 337-55-6535 72 64 62 65.00 300-60-6949 100 89 85 89.75 373-46-6412 99 88 84 88.75 397-44-6180 98 84 84 87.50 398-56-6736 100 92 86 91.00 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup
Try this:
=LOOKUP(E2,{0,1,50,60,75,90},{"","F","D","C","B"," A"}) Biff "Elizabeth" wrote in message ... Hello everyone. I need help! What I need is a VLookup formula to insert a letter grade into the last column on the second worksheet. Thank you! Elizabeth The following is on worksheet 1 Column A Col B 0 to 49 F 50 to 59 D 60 to 74 C 75 to 89 B 90 to 100 A The following is on worksheet 2 Student ID Exam 1 Exam 2 Final Overall Grade 318-84-6039 100 93 79 87.75 332-03-6854 99 90 74 84.25 341-38-6902 63 51 43 50.00 362-80-6830 65 91 81 79.50 351-25-6606 74 65 58 63.75 303-86-6698 74 63 63 65.75 360-99-6115 63 50 43 49.75 342-45-6149 84 72 65 71.50 360-49-6615 78 65 58 64.75 331-38-6683 88 78 77 80.00 337-55-6535 72 64 62 65.00 300-60-6949 100 89 85 89.75 373-46-6412 99 88 84 88.75 397-44-6180 98 84 84 87.50 398-56-6736 100 92 86 91.00 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup
Hi Biff
that worked. thank you. however, the assignment I'm working on said to use "Vlookup" specifically. Is there a difference btwn vlookup & the formula you gave me? Elizabeth "T. Valko" wrote: Try this: =LOOKUP(E2,{0,1,50,60,75,90},{"","F","D","C","B"," A"}) Biff "Elizabeth" wrote in message ... Hello everyone. I need help! What I need is a VLookup formula to insert a letter grade into the last column on the second worksheet. Thank you! Elizabeth The following is on worksheet 1 Column A Col B 0 to 49 F 50 to 59 D 60 to 74 C 75 to 89 B 90 to 100 A The following is on worksheet 2 Student ID Exam 1 Exam 2 Final Overall Grade 318-84-6039 100 93 79 87.75 332-03-6854 99 90 74 84.25 341-38-6902 63 51 43 50.00 362-80-6830 65 91 81 79.50 351-25-6606 74 65 58 63.75 303-86-6698 74 63 63 65.75 360-99-6115 63 50 43 49.75 342-45-6149 84 72 65 71.50 360-49-6615 78 65 58 64.75 331-38-6683 88 78 77 80.00 337-55-6535 72 64 62 65.00 300-60-6949 100 89 85 89.75 373-46-6412 99 88 84 88.75 397-44-6180 98 84 84 87.50 398-56-6736 100 92 86 91.00 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup
Slight difference:
=VLOOKUP(E2,{0,"";1,"F";50,"D";60,"C";75,"B";90,"A "},2) Since you have only 5 variables, it's not really necessary to create a separate lookup table. You can code that many variables in the formula itself. If this is an assignment (homework? school work?) and you are required to do specifically as directed then use Roger's suggestion. Or, if you might get extra credit (brownie points for thinking outside the box!!) use both mine and Roger's suggestions! Biff "Elizabeth" wrote in message ... Hi Biff that worked. thank you. however, the assignment I'm working on said to use "Vlookup" specifically. Is there a difference btwn vlookup & the formula you gave me? Elizabeth "T. Valko" wrote: Try this: =LOOKUP(E2,{0,1,50,60,75,90},{"","F","D","C","B"," A"}) Biff "Elizabeth" wrote in message ... Hello everyone. I need help! What I need is a VLookup formula to insert a letter grade into the last column on the second worksheet. Thank you! Elizabeth The following is on worksheet 1 Column A Col B 0 to 49 F 50 to 59 D 60 to 74 C 75 to 89 B 90 to 100 A The following is on worksheet 2 Student ID Exam 1 Exam 2 Final Overall Grade 318-84-6039 100 93 79 87.75 332-03-6854 99 90 74 84.25 341-38-6902 63 51 43 50.00 362-80-6830 65 91 81 79.50 351-25-6606 74 65 58 63.75 303-86-6698 74 63 63 65.75 360-99-6115 63 50 43 49.75 342-45-6149 84 72 65 71.50 360-49-6615 78 65 58 64.75 331-38-6683 88 78 77 80.00 337-55-6535 72 64 62 65.00 300-60-6949 100 89 85 89.75 373-46-6412 99 88 84 88.75 397-44-6180 98 84 84 87.50 398-56-6736 100 92 86 91.00 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup
Hi Elizabeth
On Sheet1, in column A you only need the first of the figures, not the range 0 F 50 D 60 C 75 B 90 A Then on Sheet2 in cell F2 =VLOOKUP(E2,Sheet1!$A$1:$B$5,2) However, I can't see how you have arrived at the values in column E. They are not arithmetic average of columns B,C and D so I assume each test must have been given a different weighting. -- Regards Roger Govier "Elizabeth" wrote in message ... Hello everyone. I need help! What I need is a VLookup formula to insert a letter grade into the last column on the second worksheet. Thank you! Elizabeth The following is on worksheet 1 Column A Col B 0 to 49 F 50 to 59 D 60 to 74 C 75 to 89 B 90 to 100 A The following is on worksheet 2 Student ID Exam 1 Exam 2 Final Overall Grade 318-84-6039 100 93 79 87.75 332-03-6854 99 90 74 84.25 341-38-6902 63 51 43 50.00 362-80-6830 65 91 81 79.50 351-25-6606 74 65 58 63.75 303-86-6698 74 63 63 65.75 360-99-6115 63 50 43 49.75 342-45-6149 84 72 65 71.50 360-49-6615 78 65 58 64.75 331-38-6683 88 78 77 80.00 337-55-6535 72 64 62 65.00 300-60-6949 100 89 85 89.75 373-46-6412 99 88 84 88.75 397-44-6180 98 84 84 87.50 398-56-6736 100 92 86 91.00 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup
If you assume that Exam 1 and Exam 2 get the same weight, then the final
counts double. Jerry "Roger Govier" wrote: However, I can't see how you have arrived at the values in column E. They are not arithmetic average of columns B,C and D so I assume each test must have been given a different weighting. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup
Well spotted Jerry!
Many thanks. -- Regards Roger Govier "Jerry W. Lewis" wrote in message ... If you assume that Exam 1 and Exam 2 get the same weight, then the final counts double. Jerry "Roger Govier" wrote: However, I can't see how you have arrived at the values in column E. They are not arithmetic average of columns B,C and D so I assume each test must have been given a different weighting. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup
Hi Roger
I used : Percentage applied to final score 1st exam 25%, 2nd exam 25%, and final exam is worth 50%. Elizabeth "Roger Govier" wrote: Hi Elizabeth On Sheet1, in column A you only need the first of the figures, not the range 0 F 50 D 60 C 75 B 90 A Then on Sheet2 in cell F2 =VLOOKUP(E2,Sheet1!$A$1:$B$5,2) However, I can't see how you have arrived at the values in column E. They are not arithmetic average of columns B,C and D so I assume each test must have been given a different weighting. -- Regards Roger Govier "Elizabeth" wrote in message ... Hello everyone. I need help! What I need is a VLookup formula to insert a letter grade into the last column on the second worksheet. Thank you! Elizabeth The following is on worksheet 1 Column A Col B 0 to 49 F 50 to 59 D 60 to 74 C 75 to 89 B 90 to 100 A The following is on worksheet 2 Student ID Exam 1 Exam 2 Final Overall Grade 318-84-6039 100 93 79 87.75 332-03-6854 99 90 74 84.25 341-38-6902 63 51 43 50.00 362-80-6830 65 91 81 79.50 351-25-6606 74 65 58 63.75 303-86-6698 74 63 63 65.75 360-99-6115 63 50 43 49.75 342-45-6149 84 72 65 71.50 360-49-6615 78 65 58 64.75 331-38-6683 88 78 77 80.00 337-55-6535 72 64 62 65.00 300-60-6949 100 89 85 89.75 373-46-6412 99 88 84 88.75 397-44-6180 98 84 84 87.50 398-56-6736 100 92 86 91.00 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup
Hi Elizabeth
Thanks for posting back with that. As you may have noticed Jerry (being a very bright mathematician) had posted with that same observation. -- Regards Roger Govier "Elizabeth" wrote in message ... Hi Roger I used : Percentage applied to final score 1st exam 25%, 2nd exam 25%, and final exam is worth 50%. Elizabeth "Roger Govier" wrote: Hi Elizabeth On Sheet1, in column A you only need the first of the figures, not the range 0 F 50 D 60 C 75 B 90 A Then on Sheet2 in cell F2 =VLOOKUP(E2,Sheet1!$A$1:$B$5,2) However, I can't see how you have arrived at the values in column E. They are not arithmetic average of columns B,C and D so I assume each test must have been given a different weighting. -- Regards Roger Govier "Elizabeth" wrote in message ... Hello everyone. I need help! What I need is a VLookup formula to insert a letter grade into the last column on the second worksheet. Thank you! Elizabeth The following is on worksheet 1 Column A Col B 0 to 49 F 50 to 59 D 60 to 74 C 75 to 89 B 90 to 100 A The following is on worksheet 2 Student ID Exam 1 Exam 2 Final Overall Grade 318-84-6039 100 93 79 87.75 332-03-6854 99 90 74 84.25 341-38-6902 63 51 43 50.00 362-80-6830 65 91 81 79.50 351-25-6606 74 65 58 63.75 303-86-6698 74 63 63 65.75 360-99-6115 63 50 43 49.75 342-45-6149 84 72 65 71.50 360-49-6615 78 65 58 64.75 331-38-6683 88 78 77 80.00 337-55-6535 72 64 62 65.00 300-60-6949 100 89 85 89.75 373-46-6412 99 88 84 88.75 397-44-6180 98 84 84 87.50 398-56-6736 100 92 86 91.00 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 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(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM? | Excel Worksheet Functions | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Hybrid Mode
