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.

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.

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.

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.