Array Function to do a Sum of VLookUps that translate a letter gra
 

Suppose I have letter grade data in a range of cells, B12:D12 as follows
A-, B-, B+/A-

I have another range of cells, named GradeList that gives numerical
equivalents for each of these letter grades. I want a function that will look
up each of these grades in the table, and return their sum, average etc.

I tried the following as an array formula:

{=SUM(VLOOKUP(B12:D12,GradeList,2,FALSE))}

And it didn't work; looks like it just returns the value of the lookup of B12.

Actually, I want this formula to sum the highest N grade values; I know how
to use an array formula to do this with numerical data in cells. But figuring
out how to do the VLookup on each letter grade cell, without having to use
separate cells to contain the numbers, and then sum on numerical equivalents
would be really helpful.

Array Function to do a Sum of VLookUps that translate a letter gra
 

On Sun, 7 Feb 2010 12:33:01 -0800, David Morris <David
wrote:

Suppose I have letter grade data in a range of cells, B12:D12 as follows
A-, B-, B+/A-

I have another range of cells, named GradeList that gives numerical
equivalents for each of these letter grades. I want a function that will look
up each of these grades in the table, and return their sum, average etc.

I tried the following as an array formula:

{=SUM(VLOOKUP(B12:D12,GradeList,2,FALSE))}

And it didn't work; looks like it just returns the value of the lookup of B12.

Actually, I want this formula to sum the highest N grade values; I know how
to use an array formula to do this with numerical data in cells. But figuring
out how to do the VLookup on each letter grade cell, without having to use
separate cells to contain the numbers, and then sum on numerical equivalents
would be really helpful.


If you name the first column of your GradeList as Grades and the
second column of your GradeList as Scores, try this formula:

=SUM(MMULT(TRANSPOSE(Scores),--(B12:D12=Grades)))

Note: This is an array formula that should be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Replace SUM with AVERAGE if that is what you want.

To get the sum of the highest N grade values try this:

=SUMPRODUCT(LARGE(MMULT(TRANSPOSE(Scores),--(B12:D12=Grades)),ROW(A1:A3)),--(ROW(A1:A3)<=N))

Note: This is an array formula that should be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Replace the N with 1,2 or 3 depending on what you need.
If you have a higher value of N than 3, and thus have a wider range of
grades than B12:D12, then you have to change the A1:A3 in two places
to be the same size as N.

Hope this helps / Lars-Åke

Array Function to do a Sum of VLookUps that translate a letter gra
 

Hi,

A different approach. You didn't say what your grade letters/numeric
equivalent are so this formula has 15 grades F- to A+ and in ascending order
each is assigned a numeric value of 1 to 15 so you should be able to modify
it. Just remember that you must keep the formula 'Balanced' 10 grades * 10
numbers etc. You can substitute AVERAGE with MIN or MAX. I haven't worked out
(yet) why SUM isn't working but I will but unfortunately have no more time
this evening.

This ARRAY formula will now averaged the numeric equivalent of the 3 grades
in your range

=INDEX({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},AVERA GE(MATCH(B12:D12,{"F-","F","F+","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"},0)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"David Morris" wrote:

Suppose I have letter grade data in a range of cells, B12:D12 as follows
A-, B-, B+/A-

I have another range of cells, named GradeList that gives numerical
equivalents for each of these letter grades. I want a function that will look
up each of these grades in the table, and return their sum, average etc.

I tried the following as an array formula:

{=SUM(VLOOKUP(B12:D12,GradeList,2,FALSE))}

And it didn't work; looks like it just returns the value of the lookup of B12.

Actually, I want this formula to sum the highest N grade values; I know how
to use an array formula to do this with numerical data in cells. But figuring
out how to do the VLookup on each letter grade cell, without having to use
separate cells to contain the numbers, and then sum on numerical equivalents
would be really helpful.

Array Function to do a Sum of VLookUps that translate a letter gra
 

Hi David Morris,

I have a workbook that takes letter grades and converts to a numerical GPA
and assigns letter average of B+, A- and such.

You put the students letter grades in his/hers column and all the
conversions are done automatic. You can adjust the value you assign to a
letter grade, D+ = 1.0, A = 3.5, F = 0 and so forth.

I can forward the workbook if you want. Reply to

Regards,
Howard

"David Morris" <David wrote in message
...
Suppose I have letter grade data in a range of cells, B12:D12 as follows
A-, B-, B+/A-

I have another range of cells, named GradeList that gives numerical
equivalents for each of these letter grades. I want a function that will
look
up each of these grades in the table, and return their sum, average etc.

I tried the following as an array formula:

{=SUM(VLOOKUP(B12:D12,GradeList,2,FALSE))}

And it didn't work; looks like it just returns the value of the lookup of
B12.

Actually, I want this formula to sum the highest N grade values; I know
how
to use an array formula to do this with numerical data in cells. But
figuring
out how to do the VLookup on each letter grade cell, without having to use
separate cells to contain the numbers, and then sum on numerical
equivalents
would be really helpful.