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?

 

[tusharm]

With 65536 rows by 256 columns and the ability to have an arbitrary number of sheets, how can aesthetics or space be an issue?

On the flip side, an array formula is less easy to understand (how many times has someone asked "what does -- mean?"), destroys an audit trail (at least in the U.S., increases pressure on XL because of SOX requirements), fails to support intermediate results leading to multiple final results (as in this discussion), and in general is more difficult to maintain.

The irony about the next point is that most of the time I am not a fan of those who worry about how to save a few keystrokes or a few CPU cycles. But the performance drag of constructs such as IF(ERROR({one-thing}),{something-else},{one-thing}) cannot be ignored.

Like I wrote, there is significant value in array formulas but they come at a price steep enough to warrant a search for simpler alternatives.

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.

 

[Domenic]

With 65536 rows by 256 columns and the ability to have an arbitrary number of sheets, how can aesthetics or space be an issue?

Good question. Nevertheless, those are some of the reasons that I've come across...

On the flip side, an array formula is less easy to understand (how many times has someone asked "what does -- mean?"), destroys an audit trail (at least in the U.S., increases pressure on XL because of SOX requirements), fails to support intermediate results leading to multiple final results (as in this discussion), and in general is more difficult to maintain.

Good point...

Like I wrote, there is significant value in array formulas but they come at a price steep enough to warrant a search for simpler alternatives.

Very well put...

Interestingly enough, I recently ran across a thread where two solutions for a Top N list were offered/compared. One solution used a helper column, and the other one used array formulas. If I remember correctly, the one using a helper column "choked" when the range exceeded 20,000 rows, whereas the other one worked fine. Unfortunately, I couldn't find the link...

 

[Fazza]

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

IMHO, it is plainly obvious - generally, and not specifically to this thread - from the questions asked that users do not know the best way to achieve their goals. They may not even know one way to get the answer, let alone the best one to suit their needs. Fundamental data & spreadsheet structure are lacking. Knowledge of Excel's functionality is lacking.

Answers posted on the forum should encourage better spreadsheets and better spreadsheet users. My 2 cents worth. Vivé la difference, Fazza

 

[Domenic]

Answers posted on the forum should encourage better spreadsheets and better spreadsheet users.

In general, I prefer to adopt Aladin's formulas and formula based approach. He follows what I like to refer to as the 'CaRE' principle...

Correctness

Robustness

Efficiency

However, one can only make suggestions and recommendations. Ultimately the rest is up to the user...

 

[Fazza]

Hi Domenic,

The CaRE principle is good. I'd like to extend it to CARE by adding Appropriateness.

I have moved away from formula solutions in recent years as I find myself working with more data - so, I guess I have a personal bias. For many of the questions asked on the forum the formulaic solutions are OK, even best; for some others - where there are much more data - alternative solutions are IMO more appropriate.

The general high quality of good, helpful & well explained answers given to posters - such as by Aladin - and the suggestion of alternative approaches are a feature of the forum. It helps all of us learn and improve our spreadsheet skills - better able to make an informed decision on the most appropriate solution in a given situation.

Regards,
Fazza

 

[Domenic]

Hi Fazza!

The CaRE principle is good. I'd like to extend it to CARE by adding Appropriateness.

This I have a problem with. "Appropriateness" is subjective. What may be appropriate for one in his/her particular circumstance may not be appropriate for another.

If efficiency is a concern, then yes, I think we can say that one method is 'better' or 'more appropriate'. Otherwise, I don't think that one should dictate in absolute terms. At least, that's the way I see it.

Often I find people aren't really concerned with efficiency. Sometimes, a user will ask right off the bat for a solution that doesn't involve helper columns and pivottables. They're specifically looking for a single cell formula.

No doubt the solution both you and Tushar have offered is more effecient. However, unless the formula I offered is going to be copied to numerous cells, efficiency isn't going to be an issue. And even so, it seems that some people are willing to bear that cost.

Cheers!

 

[Fazza]

Yes, Domenic. Appropriateness and, I would suggest, efficiency are subjective. Users will always make their own choices. There is rarely a right or wrong way; just different ways.

I don't see much dictating of solutions - though maybe I'm blind to that . I think it is good to discuss alternatives so that users learn different solutions and are informed and able to make a considered choice.

regards,
Fazza

 

[Domenic]

... Appropriateness and, I would suggest, efficiency are subjective...

Fazza, here I would have to respectively disagree. Efficiency is something that can be measured in terms of milliseconds. One could state categorically that a formula or solution calculates faster, slower, or the same compared to another. So I don't think efficiency is subjective.

Cheers!

 

[Fazza]

Fair enough, Domenic. Though now it is me respectfully disagreeing.

Smart solutions with VBA or pivot tables or query tables that one user can do are not efficient for other users.

A bit like you said above, because we all have different skill levels.

"Appropriateness" is subjective. What may be appropriate for one in his/her particular circumstance may not be appropriate for another.

If efficiency is a concern, then yes, I think we can say that one method is 'better' or 'more appropriate'.

I have done tasks in two minutes that other users either can not do at all or would take - in one instance - three months to do.