|
Changing GPA to Ltr Grade
I have this spreadsheet that merges onto a Transcript in Word€¦.We have
Student Names, Class Dates, Grades for Classes per Terms, etc.€¦.They have been manually figuring the GPA,,,,I set up some formulas that has helped, but need help on the more difficult ones€¦.someone helped me last week with the following formula (finding and matching the Grade to return a GPA. THANKS€¦€¦.Now I have been asked to find and match the Grade or GPA and return a Ltr Grade on the Transcript as well and I need help again€¦.. The following formula matches the Grade to the GPA: formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37) I tried using the same formula, just changing the Column numbers, but it didnt work€¦Can you help me? I dont know if the problem is number to text€¦.(numeric to alpha) lookup table sample is: AR AS AT Grade GPA Ltr Grade 2 100 4.0 A 3 99 4.0 A 4 98 4.0 A 5 97 4.0 A 6 96 3.7 A- 7 95 3.7 A- 8 94 3.5 B+ 9 93 3.5 B+ 10 92 3.0 B 11 91 3.0 B 12 90 3.0 B 13 89 2.7 B- 14 88 2.7 B- 15 87 2.7 B- 16 86 2.5 C+ 17 85 2.5 C+ Please write back..... Thanks, Cindy |
Changing GPA to Ltr Grade
Take a look VLOOKUP function in Help menu
"Cindy" wrote: I have this spreadsheet that merges onto a Transcript in Word€¦.We have Student Names, Class Dates, Grades for Classes per Terms, etc.€¦.They have been manually figuring the GPA,,,,I set up some formulas that has helped, but need help on the more difficult ones€¦.someone helped me last week with the following formula (finding and matching the Grade to return a GPA. THANKS€¦€¦.Now I have been asked to find and match the Grade or GPA and return a Ltr Grade on the Transcript as well and I need help again€¦.. The following formula matches the Grade to the GPA: formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37) I tried using the same formula, just changing the Column numbers, but it didnt work€¦Can you help me? I dont know if the problem is number to text€¦.(numeric to alpha) lookup table sample is: AR AS AT Grade GPA Ltr Grade 2 100 4.0 A 3 99 4.0 A 4 98 4.0 A 5 97 4.0 A 6 96 3.7 A- 7 95 3.7 A- 8 94 3.5 B+ 9 93 3.5 B+ 10 92 3.0 B 11 91 3.0 B 12 90 3.0 B 13 89 2.7 B- 14 88 2.7 B- 15 87 2.7 B- 16 86 2.5 C+ 17 85 2.5 C+ Please write back..... Thanks, Cindy |
Changing GPA to Ltr Grade
Tried this, either I don't understand how to create the formula or this isn't
the correct one to use. HELP! "Teethless mama" wrote: Take a look VLOOKUP function in Help menu "Cindy" wrote: I have this spreadsheet that merges onto a Transcript in Word€¦.We have Student Names, Class Dates, Grades for Classes per Terms, etc.€¦.They have been manually figuring the GPA,,,,I set up some formulas that has helped, but need help on the more difficult ones€¦.someone helped me last week with the following formula (finding and matching the Grade to return a GPA. THANKS€¦€¦.Now I have been asked to find and match the Grade or GPA and return a Ltr Grade on the Transcript as well and I need help again€¦.. The following formula matches the Grade to the GPA: formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37) I tried using the same formula, just changing the Column numbers, but it didnt work€¦Can you help me? I dont know if the problem is number to text€¦.(numeric to alpha) lookup table sample is: AR AS AT Grade GPA Ltr Grade 2 100 4.0 A 3 99 4.0 A 4 98 4.0 A 5 97 4.0 A 6 96 3.7 A- 7 95 3.7 A- 8 94 3.5 B+ 9 93 3.5 B+ 10 92 3.0 B 11 91 3.0 B 12 90 3.0 B 13 89 2.7 B- 14 88 2.7 B- 15 87 2.7 B- 16 86 2.5 C+ 17 85 2.5 C+ Please write back..... Thanks, Cindy |
Changing GPA to Ltr Grade
If the Grade is in D3 and the lookup range is in AR2:AT17, this VLOOKUP
formula should return the letter grade: =VLOOKUP(D3,$AR$2:$AT$17,3,FALSE) Hope this helps, Hutch "Cindy" wrote: Tried this, either I don't understand how to create the formula or this isn't the correct one to use. HELP! "Teethless mama" wrote: Take a look VLOOKUP function in Help menu "Cindy" wrote: I have this spreadsheet that merges onto a Transcript in Word€¦.We have Student Names, Class Dates, Grades for Classes per Terms, etc.€¦.They have been manually figuring the GPA,,,,I set up some formulas that has helped, but need help on the more difficult ones€¦.someone helped me last week with the following formula (finding and matching the Grade to return a GPA. THANKS€¦€¦.Now I have been asked to find and match the Grade or GPA and return a Ltr Grade on the Transcript as well and I need help again€¦.. The following formula matches the Grade to the GPA: formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37) I tried using the same formula, just changing the Column numbers, but it didnt work€¦Can you help me? I dont know if the problem is number to text€¦.(numeric to alpha) lookup table sample is: AR AS AT Grade GPA Ltr Grade 2 100 4.0 A 3 99 4.0 A 4 98 4.0 A 5 97 4.0 A 6 96 3.7 A- 7 95 3.7 A- 8 94 3.5 B+ 9 93 3.5 B+ 10 92 3.0 B 11 91 3.0 B 12 90 3.0 B 13 89 2.7 B- 14 88 2.7 B- 15 87 2.7 B- 16 86 2.5 C+ 17 85 2.5 C+ Please write back..... Thanks, Cindy |
Changing GPA to Ltr Grade
There's a way to do it using HLOOKUP, but you have to create a table that
assigns values (A+ is a 97 & a 4.0, for instance), so I don't know if you want to do that. If you do, let me know, and I'll try to explain it. =IF(E7<"",HLOOKUP(E7/100,GradeValue,2),"") "Cindy" wrote: I have this spreadsheet that merges onto a Transcript in Word€¦.We have Student Names, Class Dates, Grades for Classes per Terms, etc.€¦.They have been manually figuring the GPA,,,,I set up some formulas that has helped, but need help on the more difficult ones€¦.someone helped me last week with the following formula (finding and matching the Grade to return a GPA. THANKS€¦€¦.Now I have been asked to find and match the Grade or GPA and return a Ltr Grade on the Transcript as well and I need help again€¦.. The following formula matches the Grade to the GPA: formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37) I tried using the same formula, just changing the Column numbers, but it didnt work€¦Can you help me? I dont know if the problem is number to text€¦.(numeric to alpha) lookup table sample is: AR AS AT Grade GPA Ltr Grade 2 100 4.0 A 3 99 4.0 A 4 98 4.0 A 5 97 4.0 A 6 96 3.7 A- 7 95 3.7 A- 8 94 3.5 B+ 9 93 3.5 B+ 10 92 3.0 B 11 91 3.0 B 12 90 3.0 B 13 89 2.7 B- 14 88 2.7 B- 15 87 2.7 B- 16 86 2.5 C+ 17 85 2.5 C+ Please write back..... Thanks, Cindy |
Changing GPA to Ltr Grade
O.K., now let me explain my delima...Column AN is a total of all the grades
divided by the number of grades available (also need to see if I'm using the correct formula there---I have =SUM(D4+E4+F4+G4+H4+I4+N4+O4+P4+Q4+R4+S4+X4+Z4+AA4 +AF4+AG4+AH4)/19 -- sometimes we only have grades for First Term which is the first 6 D thru I and we have to change the 19 to 6 -- What's the correct formula for that cell???) NOW back to the first question: the Column AN which is the total of the grades divided by the number of grades sometimes gives a whole number which the formula =VLOOKUP(AN4,$AQ$2:$AS$37,3,FALSE) works wonderfully.....BUT sometimes the cell has the number 91.94736842....I have Formated the cell to a number with 0 decimal places which returns a 92 (that's what shows in the cell,,,,,,however when I use the VLOOKUP formula it gives me #N/A -- the Function Arguments Lookup_value = 91.94736842......I have even tried adding another column and doing the =AN11 (for example) but it stills pulls the same number of 91.94736842...... HELP! "SeventFloorProfessor" wrote: There's a way to do it using HLOOKUP, but you have to create a table that assigns values (A+ is a 97 & a 4.0, for instance), so I don't know if you want to do that. If you do, let me know, and I'll try to explain it. =IF(E7<"",HLOOKUP(E7/100,GradeValue,2),"") "Cindy" wrote: I have this spreadsheet that merges onto a Transcript in Word€¦.We have Student Names, Class Dates, Grades for Classes per Terms, etc.€¦.They have been manually figuring the GPA,,,,I set up some formulas that has helped, but need help on the more difficult ones€¦.someone helped me last week with the following formula (finding and matching the Grade to return a GPA. THANKS€¦€¦.Now I have been asked to find and match the Grade or GPA and return a Ltr Grade on the Transcript as well and I need help again€¦.. The following formula matches the Grade to the GPA: formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37) I tried using the same formula, just changing the Column numbers, but it didnt work€¦Can you help me? I dont know if the problem is number to text€¦.(numeric to alpha) lookup table sample is: AR AS AT Grade GPA Ltr Grade 2 100 4.0 A 3 99 4.0 A 4 98 4.0 A 5 97 4.0 A 6 96 3.7 A- 7 95 3.7 A- 8 94 3.5 B+ 9 93 3.5 B+ 10 92 3.0 B 11 91 3.0 B 12 90 3.0 B 13 89 2.7 B- 14 88 2.7 B- 15 87 2.7 B- 16 86 2.5 C+ 17 85 2.5 C+ Please write back..... Thanks, Cindy |
Changing GPA to Ltr Grade
Formatting the cell only changes how it is displayed, not the underlying
value in the cell. In the new column you mentioned, use =ROUND(AN11,0), for example, to round the grade in AN11 to a whole number. Then do your VLOOKUP using the new column value instead of the raw value in column AN. Hope this helps, Hutch "Cindy" wrote: O.K., now let me explain my delima...Column AN is a total of all the grades divided by the number of grades available (also need to see if I'm using the correct formula there---I have =SUM(D4+E4+F4+G4+H4+I4+N4+O4+P4+Q4+R4+S4+X4+Z4+AA4 +AF4+AG4+AH4)/19 -- sometimes we only have grades for First Term which is the first 6 D thru I and we have to change the 19 to 6 -- What's the correct formula for that cell???) NOW back to the first question: the Column AN which is the total of the grades divided by the number of grades sometimes gives a whole number which the formula =VLOOKUP(AN4,$AQ$2:$AS$37,3,FALSE) works wonderfully.....BUT sometimes the cell has the number 91.94736842....I have Formated the cell to a number with 0 decimal places which returns a 92 (that's what shows in the cell,,,,,,however when I use the VLOOKUP formula it gives me #N/A -- the Function Arguments Lookup_value = 91.94736842......I have even tried adding another column and doing the =AN11 (for example) but it stills pulls the same number of 91.94736842...... HELP! "SeventFloorProfessor" wrote: There's a way to do it using HLOOKUP, but you have to create a table that assigns values (A+ is a 97 & a 4.0, for instance), so I don't know if you want to do that. If you do, let me know, and I'll try to explain it. =IF(E7<"",HLOOKUP(E7/100,GradeValue,2),"") "Cindy" wrote: I have this spreadsheet that merges onto a Transcript in Word€¦.We have Student Names, Class Dates, Grades for Classes per Terms, etc.€¦.They have been manually figuring the GPA,,,,I set up some formulas that has helped, but need help on the more difficult ones€¦.someone helped me last week with the following formula (finding and matching the Grade to return a GPA. THANKS€¦€¦.Now I have been asked to find and match the Grade or GPA and return a Ltr Grade on the Transcript as well and I need help again€¦.. The following formula matches the Grade to the GPA: formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37) I tried using the same formula, just changing the Column numbers, but it didnt work€¦Can you help me? I dont know if the problem is number to text€¦.(numeric to alpha) lookup table sample is: AR AS AT Grade GPA Ltr Grade 2 100 4.0 A 3 99 4.0 A 4 98 4.0 A 5 97 4.0 A 6 96 3.7 A- 7 95 3.7 A- 8 94 3.5 B+ 9 93 3.5 B+ 10 92 3.0 B 11 91 3.0 B 12 90 3.0 B 13 89 2.7 B- 14 88 2.7 B- 15 87 2.7 B- 16 86 2.5 C+ 17 85 2.5 C+ Please write back..... Thanks, Cindy |
Changing GPA to Ltr Grade
Hi Cindy
try this formula to find the desire Grade return a Ltr Grade on your target cell. You may need to change the cell's references to suit yours. =INDEX($AT$2:$AT$20,MATCH(D3,$AR$2:$AR$100,0)) Does this do what you want? HTH -- If this posting was helpful, please click on the Yes button Thank You cheers, "Cindy" wrote: O.K., now let me explain my delima...Column AN is a total of all the grades divided by the number of grades available (also need to see if I'm using the correct formula there---I have =SUM(D4+E4+F4+G4+H4+I4+N4+O4+P4+Q4+R4+S4+X4+Z4+AA4 +AF4+AG4+AH4)/19 -- sometimes we only have grades for First Term which is the first 6 D thru I and we have to change the 19 to 6 -- What's the correct formula for that cell???) NOW back to the first question: the Column AN which is the total of the grades divided by the number of grades sometimes gives a whole number which the formula =VLOOKUP(AN4,$AQ$2:$AS$37,3,FALSE) works wonderfully.....BUT sometimes the cell has the number 91.94736842....I have Formated the cell to a number with 0 decimal places which returns a 92 (that's what shows in the cell,,,,,,however when I use the VLOOKUP formula it gives me #N/A -- the Function Arguments Lookup_value = 91.94736842......I have even tried adding another column and doing the =AN11 (for example) but it stills pulls the same number of 91.94736842...... HELP! "SeventFloorProfessor" wrote: There's a way to do it using HLOOKUP, but you have to create a table that assigns values (A+ is a 97 & a 4.0, for instance), so I don't know if you want to do that. If you do, let me know, and I'll try to explain it. =IF(E7<"",HLOOKUP(E7/100,GradeValue,2),"") "Cindy" wrote: I have this spreadsheet that merges onto a Transcript in Word€¦.We have Student Names, Class Dates, Grades for Classes per Terms, etc.€¦.They have been manually figuring the GPA,,,,I set up some formulas that has helped, but need help on the more difficult ones€¦.someone helped me last week with the following formula (finding and matching the Grade to return a GPA. THANKS€¦€¦.Now I have been asked to find and match the Grade or GPA and return a Ltr Grade on the Transcript as well and I need help again€¦.. The following formula matches the Grade to the GPA: formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37) I tried using the same formula, just changing the Column numbers, but it didnt work€¦Can you help me? I dont know if the problem is number to text€¦.(numeric to alpha) lookup table sample is: AR AS AT Grade GPA Ltr Grade 2 100 4.0 A 3 99 4.0 A 4 98 4.0 A 5 97 4.0 A 6 96 3.7 A- 7 95 3.7 A- 8 94 3.5 B+ 9 93 3.5 B+ 10 92 3.0 B 11 91 3.0 B 12 90 3.0 B 13 89 2.7 B- 14 88 2.7 B- 15 87 2.7 B- 16 86 2.5 C+ 17 85 2.5 C+ Please write back..... Thanks, Cindy |
Changing GPA to Ltr Grade
Well, to start with, you don't need SUM in that formula.
=SUM(D4+E4+F4+G4+H4+I4+N4+O4+P4+Q4+R4+S4+X4+Z4+AA4 +AF4+AG4+AH4)/19 is the same as =(D4+E4+F4+G4+H4+I4+N4+O4+P4+Q4+R4+S4+X4+Z4+AA4+AF 4+AG4+AH4)/19 Another option is =SUM(D4:I4,N4:S4,X4,Z4:AA4,AF4:AH4)/19 Interesting that you've divided by 19, rather than by 18 If you wanted an average of the non-blank entries in the range, you might want to try =AVERAGE(D4:I4,N4:S4,X4,Z4:AA4,AF4:AH4) -- David Biddulph "Cindy" wrote in message ... O.K., now let me explain my delima...Column AN is a total of all the grades divided by the number of grades available (also need to see if I'm using the correct formula there---I have =SUM(D4+E4+F4+G4+H4+I4+N4+O4+P4+Q4+R4+S4+X4+Z4+AA4 +AF4+AG4+AH4)/19 -- sometimes we only have grades for First Term which is the first 6 D thru I and we have to change the 19 to 6 -- What's the correct formula for that cell???) NOW back to the first question: the Column AN which is the total of the grades divided by the number of grades sometimes gives a whole number which the formula =VLOOKUP(AN4,$AQ$2:$AS$37,3,FALSE) works wonderfully.....BUT sometimes the cell has the number 91.94736842....I have Formated the cell to a number with 0 decimal places which returns a 92 (that's what shows in the cell,,,,,,however when I use the VLOOKUP formula it gives me #N/A -- the Function Arguments Lookup_value = 91.94736842......I have even tried adding another column and doing the =AN11 (for example) but it stills pulls the same number of 91.94736842...... HELP! "SeventFloorProfessor" wrote: There's a way to do it using HLOOKUP, but you have to create a table that assigns values (A+ is a 97 & a 4.0, for instance), so I don't know if you want to do that. If you do, let me know, and I'll try to explain it. =IF(E7<"",HLOOKUP(E7/100,GradeValue,2),"") "Cindy" wrote: I have this spreadsheet that merges onto a Transcript in Word..We have Student Names, Class Dates, Grades for Classes per Terms, etc...They have been manually figuring the GPA,,,,I set up some formulas that has helped, but need help on the more difficult ones..someone helped me last week with the following formula (finding and matching the Grade to return a GPA. THANKS...Now I have been asked to find and match the Grade or GPA and return a Ltr Grade on the Transcript as well and I need help again... The following formula matches the Grade to the GPA: formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37) I tried using the same formula, just changing the Column numbers, but it didn't work.Can you help me? I don't know if the problem is number to text..(numeric to alpha) lookup table sample is: AR AS AT Grade GPA Ltr Grade 2 100 4.0 A 3 99 4.0 A 4 98 4.0 A 5 97 4.0 A 6 96 3.7 A- 7 95 3.7 A- 8 94 3.5 B+ 9 93 3.5 B+ 10 92 3.0 B 11 91 3.0 B 12 90 3.0 B 13 89 2.7 B- 14 88 2.7 B- 15 87 2.7 B- 16 86 2.5 C+ 17 85 2.5 C+ Please write back..... Thanks, Cindy |
Changing GPA to Ltr Grade
Cindy,
Give this a shot. You may have to change the values to meet your needs. cil =IF(A189,"A",IF(A179,"B", IF(A169,"C",IF(A159,"D","F")))) "SeventFloorProfessor" wrote in message ... There's a way to do it using HLOOKUP, but you have to create a table that assigns values (A+ is a 97 & a 4.0, for instance), so I don't know if you want to do that. If you do, let me know, and I'll try to explain it. =IF(E7<"",HLOOKUP(E7/100,GradeValue,2),"") "Cindy" wrote: I have this spreadsheet that merges onto a Transcript in Word..We have Student Names, Class Dates, Grades for Classes per Terms, etc...They have been manually figuring the GPA,,,,I set up some formulas that has helped, but need help on the more difficult ones..someone helped me last week with the following formula (finding and matching the Grade to return a GPA. THANKS...Now I have been asked to find and match the Grade or GPA and return a Ltr Grade on the Transcript as well and I need help again... The following formula matches the Grade to the GPA: formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37) I tried using the same formula, just changing the Column numbers, but it didn't work.Can you help me? I don't know if the problem is number to text..(numeric to alpha) lookup table sample is: AR AS AT Grade GPA Ltr Grade 2 100 4.0 A 3 99 4.0 A 4 98 4.0 A 5 97 4.0 A 6 96 3.7 A- 7 95 3.7 A- 8 94 3.5 B+ 9 93 3.5 B+ 10 92 3.0 B 11 91 3.0 B 12 90 3.0 B 13 89 2.7 B- 14 88 2.7 B- 15 87 2.7 B- 16 86 2.5 C+ 17 85 2.5 C+ Please write back..... Thanks, Cindy |
| All times are GMT +1. The time now is 05:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com