Number of students with low grades

[tirion]

I would like to count the number of students with low grades (F), and how many of them have 1, 2, 3 or more of these.
The problem is - there could be one student with, for example three F's, but he is stll counting as only one student.
Let's say I have 22 students total (their names in ROWS) and the names of the 14 subjects (in COLUMNS).
Any suggestions?

 

[tirion]

Yes, that's it! So to sum up (for picture above):

For all students with low grades:
=SUM(IF(MMULT(--(B2:J17=1),TRANSPOSE(COLUMN(B2:J17)^0)),1))

For students with 3 or more low grades:
=SUM(IF(MMULT(--(B2:J17=1),TRANSPOSE(COLUMN(B2:J17)^0))>=3,1))

For students with 2 low grades:
=SUM(IF(MMULT(--(B2:J17=1),TRANSPOSE(COLUMN(B2:J17)^0))=2,1))

This was a big problem for me. You've saved me a lot of time.
Thank you once again.

tirion

 

[Domenic]

Yes, that's it! So to sum up (for picture above):

For all students with low grades:
=SUM(IF(MMULT(--(B2:J17=1),TRANSPOSE(COLUMN(B2:J17)^0)),1))

For students with 3 or more low grades:
=SUM(IF(MMULT(--(B2:J17=1),TRANSPOSE(COLUMN(B2:J17)^0))>=3,1))

For students with 2 low grades:
=SUM(IF(MMULT(--(B2:J17=1),TRANSPOSE(COLUMN(B2:J17)^0))=2,1))

This was a big problem for me. You've saved me a lot of time.
Thank you once again.

tirion

You're very welcome!

Cheers!

 

[tusharm]

You folks are making this way too complicated.

Option 1: Add a column that counts the number of 1s. Once you have that the rest is trivial.

Option 2: Reorganize your data into a format consistent with that for a relatinal database. Then, use various XL tools like a PivotTable or treat the data as a source for the appropriate SQL query.

Option 2 might seem complicated but option 1 is trivial. In K2 enter the formula =COUNTIF(B2:J2,1) and copy it as far down as necessary. Now, use COUNTIF(K2:K{n},x) to find the number of people with different conditions. Replace x with 2 to find the number of students with 2 1s or use ">=3" to find those with 3 or more 1s.

 

[Domenic]

You folks are making this way too complicated.

Option 1: Add a column that counts the number of 1s. Once you have that the rest is trivial.

Option 2: Reorganize your data into a format consistent with that for a relatinal database. Then, use various XL tools like a PivotTable or treat the data as a source for the appropriate SQL query.

Option 2 might seem complicated but option 1 is trivial. In K2 enter the formula =COUNTIF(B2:J2,1) and copy it as far down as necessary. Now, use COUNTIF(K2:K{n},x) to find the number of people with different conditions. Replace x with 2 to find the number of students with 2 1s or use ">=3" to find those with 3 or more 1s.

Agreed. Using a helper column is easier and usually more efficient. The problem, though, not every one likes them...

 

[vane0326]

You folks are making this way too complicated.

Option 1: Add a column that counts the number of 1s. Once you have that the rest is trivial.

Option 2: Reorganize your data into a format consistent with that for a relatinal database. Then, use various XL tools like a PivotTable or treat the data as a source for the appropriate SQL query.

Option 2 might seem complicated but option 1 is trivial. In K2 enter the formula =COUNTIF(B2:J2,1) and copy it as far down as necessary. Now, use COUNTIF(K2:K{n},x) to find the number of people with different conditions. Replace x with 2 to find the number of students with 2 1s or use ">=3" to find those with 3 or more 1s.

Agreed. Using a helper column is easier and usually more efficient. The problem, though, not every one likes them...

I agree with Domenic.

 

[Fazza]

Hi Tirion,

I think this would be trivial with the data structure improved. Put the data in three columns, Student, Subject & Grade. Can you do that?

Then any formulae or pivot tables or query tables or MS Access reports are simple.

(Otherwise, insert another column to do the counting. OK? )

regards,
Fazza

 

[tusharm]

This is not meant as commentary on the OP but if someone has a problem with simplicity why should anyone invest resources providing them with help? Other than the fact that overly complicated formulas (a) are intellectually interesting, and (b) make the "experts" feel superior?

Array formulas are a very powerful capability but using them at the drop of a hat to "save a column" is far from an appropriate use of them.

You folks are making this way too complicated.

Option 1: Add a column that counts the number of 1s. Once you have that the rest is trivial.

Option 2: Reorganize your data into a format consistent with that for a relatinal database. Then, use various XL tools like a PivotTable or treat the data as a source for the appropriate SQL query.

Option 2 might seem complicated but option 1 is trivial. In K2 enter the formula =COUNTIF(B2:J2,1) and copy it as far down as necessary. Now, use COUNTIF(K2:K{n},x) to find the number of people with different conditions. Replace x with 2 to find the number of students with 2 1s or use ">=3" to find those with 3 or more 1s.

Agreed. Using a helper column is easier and usually more efficient. The problem, though, not every one likes them...

 

[Fazza]

Sorry for covering the same matters as you Tushar. I hadn't refreshed within Explorer for a while so didn't know you'd already posted along similar lines.

Inappropriate solutions are sometimes posted. I can only imagine how much worse this could become with the next, more powerful, version of Excel.

regards,
Fazza

 

[Domenic]

This is not meant as commentary on the OP but if someone has a problem with simplicity why should anyone invest resources providing them with help? Other than the fact that overly complicated formulas (a) are intellectually interesting, and (b) make the "experts" feel superior?

Array formulas are a very powerful capability but using them at the drop of a hat to "save a column" is far from an appropriate use of them.

I don't think it's a matter of people have a problem with simplicity. But rather, other issues coming into play, such as aesthetics, available space, boss, etc. In the end, it's up to the user to determine which method best suits their needs.

 

[vane0326]

In the end, it's up to the user to determine which method best suits their needs.

Well said Domenic.