Number of students with low grades

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,578

Office Version

1. 365

Platform

1. Windows

• Jun 10, 2006

• #2

Can you provide a small sample of your data, along with expected results?

 

Upvote 0

T

tirion

New Member

Joined

Jun 10, 2006

Messages

11

• Jun 10, 2006

• #3

http://i53.photobucket.com/albums/g52/tirion_m/StudentsChart001.jpg

As you can see, there are 11 students with low grades (1) and student b has three low grades. The trick is, I presume, to count him as one student, although he has three of them. He would also be the only one in the cell: students with 3 or more low grades.

Hope this helps a bit... Still waiting for solution.

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,578

Office Version

1. 365

Platform

1. Windows

• Jun 10, 2006

• #4

For the number of students that have at least one low grade (1)...

=SUM(IF(MMULT(--(B2:O23=1),TRANSPOSE(COLUMN(B2:O23)^0)),1))

For the number of students that have three or more low grades (1)...

=SUM(IF(MMULT(--(B2:O23=1),TRANSPOSE(COLUMN(B2:O23)^0))>=3,1))

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

Upvote 0

T

tirion

New Member

Joined

Jun 10, 2006

Messages

11

• Jun 10, 2006

• #5

never mind the .jpg link, here is the html and streamlined version of the same:

Students Chart.xls
	A	B	C	D	E	F	G	H	I	J
1	students	sub1	sub2	sub3	sub4	sub5	sub6	sub7	sub8	sub9
2	a	5	5	5	5	5	5	5	5	5
3	b	2	2	4	4	2	3	1	1	1
4	d	5	4	5	5	5	1	4	4	5
5	d	5	5	5	5	5	5	5	5	5
6	e	4	5	4	5	4	5	1	3	4
7	f	2	2	4	5	2	3	2	1	4
8	g	4	4	5	5	4	5	2	2	5
9	h	5	5	5	5	5	5	5	1	5
10	i	5	5	5	5	5	5	1	5	5
11	j	4	5	5	5	5	5	4	4	5
12	k	3	3	5	5	3	1	2	2	4
13	l	5	5	5	5	5	5	5	5	5
14	m	4	4	5	5	5	5	1	3	5
15	n	5	5	5	5	5	5	4	1	5
16	o	2	4	4	2	3	2	2	1	1
17	p	5	5	5	5	5	5	5	1	5
Upis

Here we have another student (o) with 2 low grades, and he would have to be in the CELL: students with 2 low grades

Let's see...

 

Upvote 0

T

tirion

New Member

Joined

Jun 10, 2006

Messages

11

• Jun 10, 2006

• #6

Uh, sorry Domenic... I wanted to make things easier.
Give me a minute to try your formulas...
Thanks

 

Upvote 0

T

tirion

New Member

Joined

Jun 10, 2006

Messages

11

• Jun 10, 2006

• #7

This is the one. However, what if I want only the number of students with 2 low grades? When I try this with your formula, it adds both student b with 3 low grades and student o with 2 (If we look at the new picture), and I want just one with 2 low grades - student o.
Thank you very much.

 

Upvote 0

V

vane0326

Well-known Member

Joined

Aug 29, 2004

Messages

819

• Jun 10, 2006

• #8

Your saying if the number is 3 or less then there grades are F

Must be confirmed with:
Crtl+Shift+Enter

=SUM(--(MMULT(--ISNUMBER(MATCH(B2:J17,{1,2,3},0)),TRANSPOSE(COLUMN(B2:J17))^0)>0))

A non-cse
Just hit enter:

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(B2:J17,{1,2,3},0)),{1;1;1;1;1;1;1;1;1})>0))

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,578

Office Version

1. 365

Platform

1. Windows

• Jun 10, 2006

• #9

In that case, try...

=SUM(IF(MMULT(--(B2:O23=1),TRANSPOSE(COLUMN(B2:O23)^0))=2,1))

Note that =1 in blue signifies that you're interested in the low grade 1, and =2 signifies that you're interested in students with two low grades of 1.

Hope this helps!

 

Upvote 0

T

tirion

New Member

Joined

Jun 10, 2006

Messages

11

• Jun 10, 2006

• #10

No, not exactly. This is European system of evaluation (or at least it was earlier) where equivalent to best grade (A) is number 5, and to lowest grade is number 1.

Let's say we are looking at the HtmlMaker made picture above.

Number 1 is the lowest grade, and I'm interested in counting just those students with 1's. I also want number of students with low grades (11) number of those with just one low grade (9), with two low grades (1) and with three or more low grades (1).

"This is more complicated than I thought."

Still waiting...

 

Upvote 0