If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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. 
Ads 
#2




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




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




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. 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Converting RIC1 code to letter Column/Numeric Row  RidgeView  Excel Discussion (Misc queries)  5  March 8th 09 01:09 AM 
averaging class grades  sally nbct ''02  New Users to Excel  2  December 29th 08 10:16 PM 
Letter grades average  Antonio  Excel Discussion (Misc queries)  3  September 22nd 08 06:03 PM 
Changing numbers/percentages to letter grades  Preston Steele  Excel Worksheet Functions  7  January 19th 07 03:09 AM 
Converting Letter Grades to Numeric  Angelo D  Excel Worksheet Functions  6  April 25th 05 07:29 PM 