Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging a column of letter grades into a numeric GPA
In my spreadsheet, column A contains letter grades (A, A-, B+, etc.). I have
a GPA reference table in F1:G11, with F1="A", G1=4; F2="A-", G2=3.67; and so forth. Now, I know how to use VLOOKUP to turn an individual letter grade into a GPA value. In B2, I have the formula =VLOOKUP(A2,$F$1:$G$11,2,FALSE), and that works fine. What I want to do is to find the average GPA value of all the letter grades in column A. That's easy if I create a new column of data, column B, containing copies of the formula given above. But I don't want that intermediate step. How can I get the average GPA value without creating a new column of data? Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging a column of letter grades into a numeric GPA
Try this...
=SUMPRODUCT(SUMIF(F1:F5,A1:A10,G1:G5))/COUNTA(A1:A10) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... In my spreadsheet, column A contains letter grades (A, A-, B+, etc.). I have a GPA reference table in F1:G11, with F1="A", G1=4; F2="A-", G2=3.67; and so forth. Now, I know how to use VLOOKUP to turn an individual letter grade into a GPA value. In B2, I have the formula =VLOOKUP(A2,$F$1:$G$11,2,FALSE), and that works fine. What I want to do is to find the average GPA value of all the letter grades in column A. That's easy if I create a new column of data, column B, containing copies of the formula given above. But I don't want that intermediate step. How can I get the average GPA value without creating a new column of data? Many thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging a column of letter grades into a numeric GPA
David -
Use the AVERAGE function and select the range of cells containing the converted grades (that is the VLOOKUPs). -- Daryl S "David Aukerman" wrote: In my spreadsheet, column A contains letter grades (A, A-, B+, etc.). I have a GPA reference table in F1:G11, with F1="A", G1=4; F2="A-", G2=3.67; and so forth. Now, I know how to use VLOOKUP to turn an individual letter grade into a GPA value. In B2, I have the formula =VLOOKUP(A2,$F$1:$G$11,2,FALSE), and that works fine. What I want to do is to find the average GPA value of all the letter grades in column A. That's easy if I create a new column of data, column B, containing copies of the formula given above. But I don't want that intermediate step. How can I get the average GPA value without creating a new column of data? Many thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging a column of letter grades into a numeric GPA
Daryl,
That would do it, but I don't want to have a column of converted grades. Biff's solution above is more what I was looking for. Thanks, --David "Daryl S" wrote: David - Use the AVERAGE function and select the range of cells containing the converted grades (that is the VLOOKUPs). -- Daryl S "David Aukerman" wrote: In my spreadsheet, column A contains letter grades (A, A-, B+, etc.). I have a GPA reference table in F1:G11, with F1="A", G1=4; F2="A-", G2=3.67; and so forth. Now, I know how to use VLOOKUP to turn an individual letter grade into a GPA value. In B2, I have the formula =VLOOKUP(A2,$F$1:$G$11,2,FALSE), and that works fine. What I want to do is to find the average GPA value of all the letter grades in column A. That's easy if I create a new column of data, column B, containing copies of the formula given above. But I don't want that intermediate step. How can I get the average GPA value without creating a new column of data? Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting RIC1 code to letter Column/Numeric Row | Excel Discussion (Misc queries) | |||
averaging class grades | New Users to Excel | |||
Letter grades average | Excel Discussion (Misc queries) | |||
Changing numbers/percentages to letter grades | Excel Worksheet Functions | |||
Converting Letter Grades to Numeric | Excel Worksheet Functions |