Abstract

Sometimes I like to help other Excel users in Excel forums. This helps me to keep my Excel knowledge up-to-date. But some vanity surely is part of the game, too.

I am using Excel mainly for:

  1. Extract larger volumes of data from a database (Oracle or Sequel) and prepare it for reports.

  2. Automate a consecutive set of tasks, most frequently first reading Excel files or csv files, then applying some checks, transformation or aggregation of this data and finally creating report files.

  3. Normally I also have to create a proper audit trail of this process.

As so often my daily work is putting some bias to my opinion - in this case about good Excel practice: I frequently use VBA and class modules for database access, for logging, and for simple runtime optimization.

Complex worksheet formulas I normally try to avoid. On the job I never use them.

The Original Posters (OP) and Their Questions

It is quite human to post a question in several Excel forums without too much consideration. Especially if you have not found a satisfying solution on your own after some time or even after many hours.

I do not have any problem with original posters who at first

  1. cross-post their questions in several forums at the same time without cross-links.
  2. do not provide a sample file.
  3. state their problems incompletely, or who cannot accurately describe the input, the necessary transformation or calculation, or the desired output.
  4. appear to be demanding, brash, or featherbrained.

If I have enough time and if I am relaxed, I can find an incomplete problem description delightful.

In many cases you can help the original poster with a suitable Google search, for example

Excel Accurate_Problem_Description
Excel Keyword(s) filetype:xlsx
Excel VBA Accurate_Problem_Description
Excel VBA Keyword(s) filetype:xlsm
Scientific_Problem_Description filetype:pdf

The Helpers and Their Answers

Over time almost all helpers are just using their personal standard answers to their favourite topics. They repeat their approaches. I admit that I tend to do that, too.

Unfortunately many of these approaches (not mine, of course :-) ) are no good Excel practice, and many standard answers are affronting to posters who are not already familiar with these:

A Sample File would be helpful

You can often see this standard request. In many cases the original poster is asked to provide sample inputs and expected results.

In some cases this can really help the helpers to give good advice. But most questions have already been asked. Then you could provide a sample file as a suggested answer and you could ask politely whether this answers the question.

What I cannot accept: Some helpers can already judge from the question that they do not know the topic and that there is almost no chance for them to help the original poster. But they often demand a sample file.

Cross-Posting is very bad - evil you

When a less experienced user posts a question in several forums, you can bet on the fact that some helpers attack him or her and state that the rules of the forum

  1. have not been read and
  2. have not adhered to.

The stupidest allegation I ever saw was: “You do not order several cabs for just one trip.”

Another frequent allegation is that you steal helpers in other forums valuable time in case a good answer has already been provided in this one. Usually they then demand to link all questions in all different forums to each other.

These “Cross-Posting-Sheriffs” are usually circling like vultures in many forums and of course they respond quicker than any good-willing helper.

My opinion on cross posting:

  1. It should be allowed.
  2. Cross-Posting-Sheriffs have no issue to answer the same questions over time to different posters in the same way - and this quite often is a stupid one. They broach for questions in several forums anyway. I think it would be completely sufficient to answer a question with the polite hint that some forums do not favour cross posting.
  3. If you think that similar posts in different forums should be linked to each other, then it should be a link of good answers, not of unanswered questions. Again, of course you could do this with the polite hint mentioned above.
  4. My sympathy is especially low when a helper has not provided any answer for some time and only attacks in case of cross posting.

Iterative Calculation to Save a State in a Cell

The stupidest approach I know to save a state within a cell is to enable iterative calculation under File, Options, Formulas, Calculation options. Over and over again you will this horrendous stupity suggested when a user asks to store a minimum or a maximum value across multiple calculations, for example.

This approach is not advisable because you cannot fully control a reset of this calculation nor can you check when it was last done.

In addition to the above you cannot detect circular references anymore. See #6 of my Excel-Dont’s.

Worksheet Function AGGREGATE

A popular featherbrained answer is the worksheet function AGGREGATE. Often it will be suggested within a formula which spans across more than 2 rows in the formula editor.

See #11 of my Excel-Dont’s.

Look at this Wonderful One Liner

Since spreadsheets exist people are trying to develop one-cell formula solutions.

Of course you can sharpen your Excel knowledge with such formulas. But it is similar to chess problems in newspapers because you should not design spreadsheets this way, and you should not play chess this way.

Long one-cell formulas are rubbish. A good spreadsheet design can contain helper cells and it can run faster by far.

An example: Calculation of a cumulative sum.