LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 10-08-2016, 10:10 AM   #16
dave@burn-it.co.uk
Member
 
Registered: Sep 2011
Distribution: Puppy
Posts: 601

Rep: Reputation: 172Reputation: 172

By the way, it was not Excel that first did this. It was what is now Lotus 123 which pre-dates Excel.
 
Old 10-08-2016, 04:06 PM   #17
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211

Original Poster
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
Quote:
Originally Posted by dave@burn-it.co.uk View Post
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.
 
Old 10-08-2016, 04:39 PM   #18
dave@burn-it.co.uk
Member
 
Registered: Sep 2011
Distribution: Puppy
Posts: 601

Rep: Reputation: 172Reputation: 172
Well do you tell them they are wrong, or is it just because they want to MS bash??
 
Old 10-08-2016, 06:34 PM   #19
rkelsen
Senior Member
 
Registered: Sep 2004
Distribution: slackware
Posts: 3,129
Blog Entries: 4

Rep: Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361
Smile

Quote:
Originally Posted by Michael Uplawski View Post
I want a cell-reference to an empty cell to be an empty cell or an explanation why it has to be different.
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.

Last edited by rkelsen; 10-08-2016 at 08:04 PM.
 
Old 10-09-2016, 03:09 AM   #20
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211

Original Poster
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
Quote:
Originally Posted by rkelsen View Post
Sorry for the late response.
What? :-)

Quote:
Basically, there are two things happening here:
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.
 
Old 10-09-2016, 07:14 AM   #21
rkelsen
Senior Member
 
Registered: Sep 2004
Distribution: slackware
Posts: 3,129
Blog Entries: 4

Rep: Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361
Quote:
Originally Posted by Michael Uplawski View Post
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”.
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.
 
Old 10-09-2016, 03:44 PM   #22
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211

Original Poster
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
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.

Quote:
Originally Posted by rkelsen View Post
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?
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.

Quote:
Specifically, what do you need to do? What output do you need?
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.

Quote:
Experience has taught me that suppressing zeros is (generally) a bad practice.
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.

Last edited by Michael Uplawski; 10-09-2016 at 03:57 PM.
 
Old 10-09-2016, 03:52 PM   #23
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211

Original Poster
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
Quote:
Originally Posted by dave@burn-it.co.uk View Post
Well do you tell them they are wrong, or is it just because they want to MS bash??
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.
 
Old 10-09-2016, 06:33 PM   #24
rkelsen
Senior Member
 
Registered: Sep 2004
Distribution: slackware
Posts: 3,129
Blog Entries: 4

Rep: Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361Reputation: 1361
Quote:
Originally Posted by Michael Uplawski View Post
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.
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.
Quote:
Originally Posted by Michael Uplawski View Post
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.

...

This suppressing zeros is only for the eyes of the reader.
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.
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: 4 Spreadsheet Alternatives to MS Excel LXer Syndicated Linux News 0 04-05-2014 05:42 PM
Emailing LibreOffice Spreadsheet As Excel roger2143 Linux - Newbie 3 09-05-2012 07:30 PM
Saving in excel spreadsheet format vladimir1986 Linux - Desktop 5 12-13-2011 02:28 PM
Save data in excel spreadsheet? Shioni Programming 3 07-29-2006 08:15 PM
mySQL and excel spreadsheet zuessh Linux - Software 1 06-12-2003 07:26 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 02:31 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration