Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem
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 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula percentage problem | New Users to Excel | |||
formula problem | New Users to Excel | |||
Incremental formula problem | Excel Worksheet Functions | |||
Problem with nested logical formula. | Excel Worksheet Functions | |||
problem with Array Formula | Excel Worksheet Functions |