Formula Problem

I've got a range of grades in cols D:M e.g B,C,A,D,F,B,C,A,A,E

and a lookup table named "Points"
A 6
B 5
C 4
D 3
E 2
F 1

In Col N I'm trying to create a formula which adds the points for each grade
in Cols D:M

Using the example above the answer would be 42 but I'm stuck on how to
create a formula to calculate it.

Any help would be much appreciated

Thanks a lot

Try this:
For grades in Cells D1:M1
N1: =SUMPRODUCT(SEARCH(D1:M1,"FEDCBA"))

Does that help?

***********
Regards,
Ron


"nospaminlich" wrote:

> I've got a range of grades in cols D:M e.g B,C,A,D,F,B,C,A,A,E
>
> and a lookup table named "Points"
> A 6
> B 5
> C 4
> D 3
> E 2
> F 1
>
> In Col N I'm trying to create a formula which adds the points for each grade
> in Cols D:M
>
> Using the example above the answer would be 42 but I'm stuck on how to
> create a formula to calculate it.
>
> Any help would be much appreciated
>
> Thanks a lot

Hi,

Let us suppose that the grades are D2, E2, ..... M2, and the lookup table is
in the range, say A2:B7). In N2 enter the formula,

=SUMPRODUCT((D2:M2=$A$2:$A$7)*$B$2:$B$7)

If you have grade-data in more rows below Row 2 (i.e., D2:D101, E2:E101,
....., M2:M101, for several students), drag the formula in N2 down the column
to N101.

Regards,
B. R. Ramachandran

"nospaminlich" wrote:

> I've got a range of grades in cols D:M e.g B,C,A,D,F,B,C,A,A,E
>
> and a lookup table named "Points"
> A 6
> B 5
> C 4
> D 3
> E 2
> F 1
>
> In Col N I'm trying to create a formula which adds the points for each grade
> in Cols D:M
>
> Using the example above the answer would be 42 but I'm stuck on how to
> create a formula to calculate it.
>
> Any help would be much appreciated
>
> Thanks a lot

Hi!

Invert your lookup table such that:

F
E
D
C
B
A

Then: (assuming there are no empty cells in the range D1:M1)

=SUMPRODUCT(MATCH(D1:M1,A1:A6,0))

Biff

"nospaminlich" <nospaminlich@discussions.microsoft.com> wrote in message
news:75AF4B07-53DF-4B82-8FC1-2CF712D66DD3@microsoft.com...
> I've got a range of grades in cols D:M e.g B,C,A,D,F,B,C,A,A,E
>
> and a lookup table named "Points"
> A 6
> B 5
> C 4
> D 3
> E 2
> F 1
>
> In Col N I'm trying to create a formula which adds the points for each
> grade
> in Cols D:M
>
> Using the example above the answer would be 42 but I'm stuck on how to
> create a formula to calculate it.
>
> Any help would be much appreciated
>
> Thanks a lot

Assuming letter grades are in D1:M1 and lookup table is A1:A6

In D2 enter =VLOOKUP(D1,$A$1:$A$6,2,FALSE)

Drag across to M2

In N2 enter =SUM(D2:M2) returns 42 as you state in your description.

But that does sum up the total for "each" grade.


Gord Dibben Excel MVP

On Thu, 1 Dec 2005 15:41:02 -0800, nospaminlich
<nospaminlich@discussions.microsoft.com> wrote:

>I've got a range of grades in cols D:M e.g B,C,A,D,F,B,C,A,A,E
>
>and a lookup table named "Points"
>A 6
>B 5
>C 4
>D 3
>E 2
>F 1
>
>In Col N I'm trying to create a formula which adds the points for each grade
>in Cols D:M
>
>Using the example above the answer would be 42 but I'm stuck on how to
>create a formula to calculate it.
>
>Any help would be much appreciated
>
>Thanks a lot

Thanks a lot for all the ideas. I've gone with B.R.'s suggestion in the end
but all the ideas were helpful. Thanks again