Using Excel to calculate Credits

knaimi

New Member
Joined
Jun 23, 2013
Messages
49
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:

CounselorGrade LevelIDLastFirstPeriodCourse-IDCourse TitleTeacherRoomFinalCredit
9ID 1Last 1First 11623000Biology S2Mr. MA204BA
1.5​
9ID 1Last 1First 12611010Language Arts 1Mrs. DB-11B-
1.5​
9ID 1Last 1First 13617070EIntro to Art 1Ms. AE-12E
1.5​
9ID 1Last 1First 146325987Algebra 1 S2Mr. LC-04G
1.5​
9ID 2 Last 2First 21623000Biology S2Mr. MA204BG
1​
9ID 2Last 2First 22611010Language Arts 1Mrs. DB-11G
1​
9ID 2Last 2First 23617070EIntro to Art 1Ms. AE-12U
1​
9ID 2Last 2First 246325987EAlgebra 1 S2Mr. LC-04E
1​

Thank you,
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi

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

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"
 
Upvote 0
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.
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGHIJKL
1CounselorGrade LevelIDLastFirstPeriodCourse-IDCourse TitleTeacherRoomFinalCredit
29ID 1Last 1First 11623000Biology S2Mr. MA204BA1.5
39ID 1Last 1First 12611010Language Arts 1Mrs. DB-11B-1.5
49ID 1Last 1First 13617070EIntro to Art 1Ms. AE-12E1.5
59ID 1Last 1First 146325987Algebra 1 S2Mr. LC-04G1.5
69ID 2Last 2First 21623000Biology S2Mr. MA204BG1
79ID 2Last 2First 22611010Language Arts 1Mrs. DB-11G1
89ID 2Last 2First 23617070EIntro to Art 1Ms. AE-12U1
99ID 2Last 2First 246325987EAlgebra 1 S2Mr. LC-04E1
Data
Cell Formulas
RangeFormula
L2:L9L2=COUNTIFS(C:C,C2,K:K,"<>E",K:K,"<>U")*0.5
 
Upvote 0
Solution
How about
+Fluff 1.xlsm
ABCDEFGHIJKL
1CounselorGrade LevelIDLastFirstPeriodCourse-IDCourse TitleTeacherRoomFinalCredit
29ID 1Last 1First 11623000Biology S2Mr. MA204BA1.5
39ID 1Last 1First 12611010Language Arts 1Mrs. DB-11B-1.5
49ID 1Last 1First 13617070EIntro to Art 1Ms. AE-12E1.5
59ID 1Last 1First 146325987Algebra 1 S2Mr. LC-04G1.5
69ID 2Last 2First 21623000Biology S2Mr. MA204BG1
79ID 2Last 2First 22611010Language Arts 1Mrs. DB-11G1
89ID 2Last 2First 23617070EIntro to Art 1Ms. AE-12U1
99ID 2Last 2First 246325987EAlgebra 1 S2Mr. LC-04E1
Data
Cell Formulas
RangeFormula
L2:L9L2=COUNTIFS(C:C,C2,K:K,"<>E",K:K,"<>U")*0.5
Thanks, Fluff. You nailed it!!!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top