PDA

View Full Version : Making an Auxiliary Table For Grade Distibution



harber95
08-10-2015, 03:06 AM
14120

I have a table in Sheet1. I am supposed to count how many student got certain grade in English (column G) with the ranges presented in Sheet2. After I count them the quantity of students should appear in Sheet2.

Also, I have the option to add students to Sheet1. So I guess there are loops involved. Thanks or help.

mancubus
08-10-2015, 04:06 AM
all you need is, inserting the formulas below into cells B2:B6 in Sheet2.
select all formulas and copy to C2:E6



=COUNTIFS(Sheet1!G:G,">=86")

=COUNTIFS(Sheet1!G:G,"<=85",Sheet1!G:G,">=66")

=COUNTIFS(Sheet1!G:G,"<=65",Sheet1!G:G,">=51")

=COUNTIFS(Sheet1!G:G,"<=50",Sheet1!G:G,">=36")

=COUNTIFS(Sheet1!G:G,"<=35")

harber95
08-10-2015, 04:32 AM
I'm a newbie. What do I write before the "=" marks?

Aussiebear
08-10-2015, 04:50 AM
Nothing at all. Excel recognises the "=" as the staring point for an equation

harber95
08-10-2015, 04:59 AM
I got the following error message after copying your code:

"Expected: line number or label of statement of end of statement".

Maybe I'm supposed to write something before the code you gave me?

mancubus
08-10-2015, 05:04 AM
these are worksheet formulas. you don't need vba.

Aussiebear
08-10-2015, 05:12 AM
I got the following error message after copying your code:

What code? You were given 5 formulas to use.

harber95
08-10-2015, 08:56 AM
My bad, But I must use vba for this one (assignment)

Paul_Hossler
08-10-2015, 11:10 AM
I must use vba for this one (assignment)



Homework???

There's many other issues with your workbook, but here's a simple demo sub using loops. Look in online help for things like .CurrentRegion, and .Resize and With .... End With and Select Case ... End Select


There's other more 'Excel-like' ways, but this would be an example of a pure VBA approach



Option Explicit
Sub SumGrades()
Dim rStudents As Range, rGrades As Range
Dim iStudent As Long, iGrade As Long


Set rStudents = Worksheets("Sheet1").Cells(1, 1).CurrentRegion
Set rGrades = Worksheets("Sheet2").Cells(1, 1).CurrentRegion

With rGrades
.Cells(2, 2).Resize(.Rows.Count - 1, .Columns.Count - 1).Clear
End With

With rStudents
For iStudent = 2 To .Rows.Count
For iGrade = 7 To 10
Select Case .Cells(iStudent, iGrade).Value
Case 86 To 100
rGrades.Cells(2, iGrade - 5).Value = rGrades.Cells(2, iGrade - 5).Value + 1
Case 66 To 85
rGrades.Cells(3, iGrade - 5).Value = rGrades.Cells(3, iGrade - 5).Value + 1
Case 51 To 65
rGrades.Cells(4, iGrade - 5).Value = rGrades.Cells(4, iGrade - 5).Value + 1
Case 36 To 50
rGrades.Cells(5, iGrade - 5).Value = rGrades.Cells(5, iGrade - 5).Value + 1
Case Else
rGrades.Cells(6, iGrade - 5).Value = rGrades.Cells(6, iGrade - 5).Value + 1
End Select
Next iGrade
Next iStudent
End With
End Sub

Aussiebear
08-10-2015, 07:25 PM
My bad, But I must use vba for this one (assignment)

Is this actually homework thats driving these posts? I can't help the feeling that your knowledge of the object layer of excel is extremely poor, and that help given here has been to overcome an issue rather than to assist you in the learning of vba.

harber95
08-10-2015, 10:49 PM
Actually, I'm a Chemistry student and I had to take a course in Excel.
This year, they changed the nature of this course, by giving a major project and a mini-project instead of just a test.
To be honest, I did try to find help by googling the issues, but it didn't help much for me, so I came here. It's just this subject is all new to me and came as a shock. Thanks for the helper, by the way. Code works like a charm.

harber95
08-10-2015, 11:35 PM
One more thing, I want to add another line for the grade range of 0-35. I tried to add it, but it didn't work.

Aussiebear
08-11-2015, 01:15 AM
Thank you for being honest with us. It was quite clear that you are well out of your depth with excel. Normally this forum has a policy of assisting those doing homework/ Assignments slightly different. However since you have gotten this far, where are you trying to add the line for the grade range?

harber95
08-11-2015, 02:06 AM
To this code:


Option Explicit
Sub SumGrades()
Dim rStudents As Range, rGrades As Range
Dim iStudent As Long, iGrade As Long


Set rStudents = Worksheets("Sheet1").Cells(1, 1).CurrentRegion
Set rGrades = Worksheets("Sheet2").Cells(1, 1).CurrentRegion

With rGrades
.Cells(2, 2).Resize(.Rows.Count - 1, .Columns.Count - 1).Clear
End With

With rStudents
For iStudent = 2 To .Rows.Count
For iGrade = 7 To 10
Select Case .Cells(iStudent, iGrade).Value
Case 86 To 100
rGrades.Cells(2, iGrade - 5).Value = rGrades.Cells(2, iGrade - 5).Value + 1
Case 66 To 85
rGrades.Cells(3, iGrade - 5).Value = rGrades.Cells(3, iGrade - 5).Value + 1
Case 51 To 65
rGrades.Cells(4, iGrade - 5).Value = rGrades.Cells(4, iGrade - 5).Value + 1
Case 36 To 50
rGrades.Cells(5, iGrade - 5).Value = rGrades.Cells(5, iGrade - 5).Value + 1
Case Else
rGrades.Cells(6, iGrade - 5).Value = rGrades.Cells(6, iGrade - 5).Value + 1
End Select
Next iGrade
Next iStudent
End With
End Sub

Paul_Hossler
08-11-2015, 07:00 AM
I think the Case Else catches the 0 - 35 range. I had to add some low grades to your data to test

Are there some that slip through?





Select Case .Cells(iStudent, iGrade).Value
Case 86 To 100
rGrades.Cells(2, iGrade - 5).Value = rGrades.Cells(2, iGrade - 5).Value + 1
Case 66 To 85
rGrades.Cells(3, iGrade - 5).Value = rGrades.Cells(3, iGrade - 5).Value + 1
Case 51 To 65
rGrades.Cells(4, iGrade - 5).Value = rGrades.Cells(4, iGrade - 5).Value + 1
Case 36 To 50
rGrades.Cells(5, iGrade - 5).Value = rGrades.Cells(5, iGrade - 5).Value + 1
Case Else
rGrades.Cells(6, iGrade - 5).Value = rGrades.Cells(6, iGrade - 5).Value + 1
End Select



Again, the Excel VBA online help has a pretty good explanation and examples

harber95
08-11-2015, 01:27 PM
14128

the line with the low grades is missing. Also, I don't know how to make a total count for all subjects in column F. Thanks again

Paul_Hossler
08-11-2015, 02:29 PM
1. There are no grades in any subject lower than a 40. That's why like I said " I had to add some low grades to your data to test"

2. In VBA you would make the total count just the same way that you would do it by hand:
Go down each row and add the 4 numbers and put the total in column F

The With rGrades is just a VBA shorthand to avoid typing and for clarity since all of the 'dot' keywords apply to it



With rGrades
For iGrade = 2 To .Rows.Count
.Cells(iGrade, 6).Value = .Cells(iGrade, 2).Value + _
.Cells(iGrade, 3).Value + _
.Cells(iGrade, 4).Value + _
.Cells(iGrade, 5).Value
Next iGrade
End With




I have to agree with Aussiebear --


It was quite clear that you are well out of your depth with excel. Normally this forum has a policy of assisting those doing homework/ Assignments slightly different. However since you have gotten this far, where are you trying to add the line for the grade range?

You'd be better served by getting an 'Intro to Excel' and an 'Intro to VBA' book from the local book store. That way you'd have a much more organized and structured learning experience

harber95
08-11-2015, 02:48 PM
Thanks for the tip. I used macro instead.