Remember Me?

#1
September 22nd 09, 07:36 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2009 Posts: 14
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
September 22nd 09, 07:55 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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
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
September 22nd 09, 07:59 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2009 Posts: 135
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
September 22nd 09, 08:31 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2009 Posts: 14
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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post RidgeView Excel Discussion (Misc queries) 5 March 8th 09 12:09 AM sally nbct ''02 New Users to Excel 2 December 29th 08 09:16 PM Antonio Excel Discussion (Misc queries) 3 September 22nd 08 06:03 PM Preston Steele Excel Worksheet Functions 7 January 19th 07 02:09 AM Angelo D Excel Worksheet Functions 6 April 25th 05 07:29 PM

All times are GMT +1. The time now is 03:45 PM.