By the way, it was not Excel that first did this. It was what is now Lotus 123 which pre-dates Excel.

I know, that it was Lotus 1-2-3. But the answer I get when I ask about a certain behavior does always refer to Excel.

Well do you tell them they are wrong, or is it just because they want to MS bash??

Sorry for the late response.

Basically, there are two things happening here:

1. A reference to an empty cell will always return a "0". I cannot say the reason for this with 100% certainty... but I assume that this happens so that any other formulas referencing the output will work properly. If it weren't for this, you could end up with a data flow that stops working. I never really thought about it, and I guess it is a little weird, but I think it makes sense in most cases.

2. The cells you're copying into will always retain the original cell's format. This also makes sense to me. Individual (or small groups of) cells with different formulas or formats to the rest of the columns or rows in your data tables will result in hours of debugging and hair pulling, usually when it's least convenient.

What? :-)

Thank you for the complete summary. It is especially the second point, to which I have not put enough attention, in the beginning (... of my reflections.., this thread... etc.) In the meantime, and as I was having the very same discussion in a different universe, using a very different language, my “wish” for a future improvement has evolved into this:

“Spreadsheet programs, especially those which already give you the possibility to individually suppress 0s, should allow the very same for cells formatted for calendar dates”.
For a program like Gnumeric, this is a big inconvenience and will probably not be implemented any time soon. The others should quite easily be adaptable.

OK. The question I have is why do you need to suppress them in a way other than the IF formula provided in a post above?

Specifically, what do you need to do? What output do you need?

A large part of my job is generating spreadsheets. I have to produce output which is transparent and easily verified. Experience has taught me that suppressing zeros is (generally) a bad practice.

Again, a long post prepared in an external editor. I am afraid, my English is
not versatile enough to pinpoint simple things with simple words. Believe me,
that this bugs me, too.

The word which offends me in this question is ‘need’. Maybe there are
mis-interpretations of my posts. What I ‘need’, personally, has
actually not been formulated.
Instead, I tried to describe a fact that, in my opinion, gives evidence of
either a contradiction or a flaw in the way that spreadsheet programs are
conceived.

I do face inconvenience and make an effort to avoid dumb errors due to
this reality. But this is only temporary, as the specific task that I will
describe further below, will not occupy me longer than for a few weeks.

Up to this point, I have tried to concentrate on the technology. Now I fear
that to make my response comprehensible to all, we must first erase from our
memory all the evidence and good advice that the discussion, above, has
already produced.

So, do as if this were the initial post.

In a workbook of many tables, many sequences of calendar dates are reproduced
at least twice on the same sheet. These sequences comprise between two and
twelve dates per sheet, always in rows where the dates are preceded and
succeeded by other kind of data, mostly text.

I can use formulas and I can erase those references to dates which appear to
be superfluous. But anyway, the work-flow is best accelerated by using the
mouse to transfer the said references. In this context, avoiding superfluous
references to empty cells is not always simple, as you do not see the original
dates while you draw the mouse. Fixating the line of dates, then “unfreezing”
them again afterwards, to avoid these unneeded references, adds an unnecessary
complication, in my opinion; so I do not do it.

The workbook that I am working on is not my invention, nor what I would have
recommended to do the job: organize the orders of organic food in a network of
farmers, beekeepers, bakers or similar and their customers. I do not have the
desire to revolutionize a procedure which is established and mastered since
long before I joined the very network.

Edit: Furthermore, it is *their* creation and proves their will to
organize their own flavor of resistance. Not paying an external contractor
or other kind of payed assistant is part of the subversive action.

However, as it is always with this kind of document and in this kind of
environment, the stuff rots. I have been identified as someone who
theoretically disposes of the knowledge to ease out some of the glitches which
raises difficulties to some of the people involved. What I am confronted with
is an agglomeration of all kinds of questionable Excel-knowledge, joined
cells, where none are needed, line-breaks where distinct values are squeezed
into one cell and separated later with the help of a formula etc.

In my struggle to correct and simplify the file, I also modify the
organization of delivery dates. This is where I need to “duplicate”
date-values twice on each sheet and chose to use references instead, for
security.

Technically, there is no need to suppress zeros in the worksheet I am
working on.

But some of the “users” have had trouble producing readable printouts and
eliminating all the zeros augments readability of the orders, even in
these cases. I am not talking about a group of computer enthusiasts and
attending a course before being able to command 15kg of white flour, is
out of the question. This suppressing zeros is only for the eyes of the
reader.

I repeat myself: Personally, I am fine with all the conclusions. And for
the time being, I will use a user-defined date-format to get rid of
the 0-dates. Selectively.

In the communities that my questions have been answered in, Lotus does not matter and has probably never been mentioned, before. Most of these people know Excel. It is like when “The Web” was represented by “Internet Exploder” and “Writing Stuff” with MS Word or now, that “EMail” is “GMail”. Spreadsheet is Excel. Even if we all use Linux.

Please accept my apologies. I certainly did not intend to be offensive.

By establishing exactly what you need, I'll be able to try to help you get there.
The problems you describe are very common:

• The input you receive is messy and inconsistent,
• Your users demand clean and consistent output, and,
• They have little understanding of how to get it.

You can fix these problems with some engineering to deliver clean, consistent output.

My preferred method of attack in this situation is to use a 3 step model:

1. Take the messy and inconsistent input,
2. Apply some logic to "sanitise" the data,
3. Present the sanitised data to the end-user in a format they choose.

The user sees only the end result, but behind the scenes there are 3 or 4 hidden tabs which contain the "mess." The original data remains intact and in the same condition as you received it.

If you have a certain user who always gets something wrong (and there is always one), you can use the "logic" step to easily work around them.

(As an aside, a couple of years ago I attended a spreadsheeting course as part of my CPD (required by work), and didn't expect much from it... but, boy did it change my life. As I mentioned above, spreadsheets form a big part of my working life, and after 15 years working with them I didn't think there was much more I could learn. The course opened my eyes to a smarter way of working with spreadsheets, using the 3-step model mentioned above in combination with a handful of simple-but-powerful functions (and NO macros) to generate multi-layered reporting which gives the user exactly what they want. Before attending the course, I had never really stood back and thought about the way I was using spreadsheets. In the years since, it has saved me a lot of time and frustration.)

1 members found this post helpful.