How to convert letter grade to number in Excel?
If you have a sheet which contains student names and the letter grades, now you want to convert the letter grades to the relative number grades as below screenshot shown. You can convert them one by one, but it is time-consuming while there are so many to convert. Here I introduce two methods to quickly deal with this job in Excel.
Before using VLOOKUP function to convert letter grade to number, you need to create a sheet which contains letter grades and their relative number grades as below screenshot shown:
Select the cell you want to place the number grade, type this formula
Press Enter key. Then drag the fill handle down to apply this formula to cells.
In the formula, B2 is the letter grade you want to convert to number, GPA is the sheet name contains number grade, $A$2:$B$14 is the letter grade and number grade column range, 2 indicate to return value in the second column.
With the formula, you need to change the refences to match your data, which may cause some mistake if you are not familiar with formulas. In this case, you can try below method, what you need to do is select data range and output range, the rest is up to the handy tool.
There is a group tools of Super LOOKUP in Kutools for Excel, you can use its LOOKUP Across Multiple Sheets tool to quickly handle this job.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
After free installing Kutools for Excel, please do as below:
1. Click Kutools > Super LOOKUP > LOOKUP Across Multiple Sheets.
2. In the LOOKUP Across Multiple Sheets dialog, do as below:
1. Select the lookup values range and output range.
2. Click Add button to add the data range, in the Add Data Range dialog, specify the key column and return column. Then click OK, the data range has been list in the LOOKUP Across Multiple Sheets dialog.
3. Click OK, now the results have been displayed. At the same time, a dialog pops out to ask you if save this lookup operation as scenario for reuse next time, click Yes or No as you need.
Combine rows based on another column value in Excel
|Here is a table which contains two columns, one is the student names, another is the grade letter.What the job is to combine the names based on the same grade letter. In Excel, there is no built-in function can quickly solve it, while Kutools for Excel's Advanced Combine Rows utility can do you a favor. You can combine rows based on a key column, also can calcualte rows based on column. Click for 30 days free trial!|
|Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|