Using Excel to calculate Credits

[knaimi]

I downloaded a report in Excel which includes students information like their ID #, Last name, first name, subject, and final grade ...etc. Each row will have the name of the student and the subject he/she has took. In the last column, I want to add a formula that will look at the student's final grade and if it's between A+ to D- or a "G" then the student should receive 0.5 credit for each subject. If the final grade is E or U then the student will receive 0 credit. The formula should then add up the credits and add the sum in each row. Please see example below:

Counselor	Grade Level	ID	Last	First	Period	Course-ID	Course Title	Teacher	Room	Final	Credit
	9	ID 1	Last 1	First 1	1	623000	Biology S2	Mr. M	A204B	A	1.5​
	9	ID 1	Last 1	First 1	2	611010	Language Arts 1	Mrs. D	B-11	B-	1.5​
	9	ID 1	Last 1	First 1	3	617070E	Intro to Art 1	Ms. A	E-12	E	1.5​
	9	ID 1	Last 1	First 1	4	6325987	Algebra 1 S2	Mr. L	C-04	G	1.5​
	9	ID 2	Last 2	First 2	1	623000	Biology S2	Mr. M	A204B	G	1​
	9	ID 2	Last 2	First 2	2	611010	Language Arts 1	Mrs. D	B-11	G	1​
	9	ID 2	Last 2	First 2	3	617070E	Intro to Art 1	Ms. A	E-12	U	1​
	9	ID 2	Last 2	First 2	4	6325987E	Algebra 1 S2	Mr. L	C-04	E	1​

Thank you,

 

[SQUIDD]

Hi

So should row 3, 7 and 8 not actually be 0 credits in your example?

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Counselor", type any}, {"Grade Level", Int64.Type}, {"ID", type text}, {"Last", type text}, {"First", type text}, {"Period", Int64.Type}, {"Course-ID", type any}, {"Course Title", type text}, {"Teacher", type text}, {"Room", type text}, {"Final", type text}, {"Credit", type any}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Final], "A") then 0.5 else if Text.Contains([Final], "B") then 0.5 else if Text.Contains([Final], "C") then 0.5 else if Text.Contains([Final], "D") then 0.5 else if Text.Contains([Final], "G") then 0.5 else 0),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Last", "First"}, {{"Total", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"

 

[knaimi]

Hi

So should row 3, 7 and 8 not actually be 0 credits in your example?

Correct, but the credit column should add up the credits each student received. So the first student passed three class then he/she will receive 1.5 credits which should show up on the rows.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Counselor	Grade Level	ID	Last	First	Period	Course-ID	Course Title	Teacher	Room	Final	Credit
2		9	ID 1	Last 1	First 1	1	623000	Biology S2	Mr. M	A204B	A	1.5
3		9	ID 1	Last 1	First 1	2	611010	Language Arts 1	Mrs. D	B-11	B-	1.5
4		9	ID 1	Last 1	First 1	3	617070E	Intro to Art 1	Ms. A	E-12	E	1.5
5		9	ID 1	Last 1	First 1	4	6325987	Algebra 1 S2	Mr. L	C-04	G	1.5
6		9	ID 2	Last 2	First 2	1	623000	Biology S2	Mr. M	A204B	G	1
7		9	ID 2	Last 2	First 2	2	611010	Language Arts 1	Mrs. D	B-11	G	1
8		9	ID 2	Last 2	First 2	3	617070E	Intro to Art 1	Ms. A	E-12	U	1
9		9	ID 2	Last 2	First 2	4	6325987E	Algebra 1 S2	Mr. L	C-04	E	1
Data

Cell Formulas
Range	Formula
L2:L9	L2	=COUNTIFS(C:C,C2,K:K,"<>E",K:K,"<>U")*0.5

 

[knaimi]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Counselor	Grade Level	ID	Last	First	Period	Course-ID	Course Title	Teacher	Room	Final	Credit
2		9	ID 1	Last 1	First 1	1	623000	Biology S2	Mr. M	A204B	A	1.5
3		9	ID 1	Last 1	First 1	2	611010	Language Arts 1	Mrs. D	B-11	B-	1.5
4		9	ID 1	Last 1	First 1	3	617070E	Intro to Art 1	Ms. A	E-12	E	1.5
5		9	ID 1	Last 1	First 1	4	6325987	Algebra 1 S2	Mr. L	C-04	G	1.5
6		9	ID 2	Last 2	First 2	1	623000	Biology S2	Mr. M	A204B	G	1
7		9	ID 2	Last 2	First 2	2	611010	Language Arts 1	Mrs. D	B-11	G	1
8		9	ID 2	Last 2	First 2	3	617070E	Intro to Art 1	Ms. A	E-12	U	1
9		9	ID 2	Last 2	First 2	4	6325987E	Algebra 1 S2	Mr. L	C-04	E	1
Data

Cell Formulas
Range	Formula
L2:L9	L2	=COUNTIFS(C:C,C2,K:K,"<>E",K:K,"<>U")*0.5

Thanks, Fluff. You nailed it!!!

 

[Fluff]

You're welcome & thanks for the feedback.