[Solved] Apache OpenOffice hanging in Lubuntu

Issues with installing under all Linux Distributions

[Solved] Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Thu Jan 02, 2020 6:14 pm

Hello, new member here. Long time (10-yr.) satisfied Open Office user. Have found many answers to many issues in these forums through the years just browsing as an unregistered user. I have two computers running Apache OpenOffice 4.1.4 64-bit (Panasonic Toughbook CF-31, Lubuntu 18.04.3LTS 64-bit, Intel I5-2520M 2.50 Ghz, 8Gb RAM, 120 Gb Hdd) and (Dell Precision T1700, Lubuntu 17.04.3LTS 64-bit, Intel Xeon E3-1226V3 3.30 Ghz, 16Gb RAM, 500Gb SS HD).

The issue is that Open Office randomly hangs and becomes unresponsive. The operating systems and OpenOffice software are fresh installations on both computers. Open Office works great, until it hangs. It hangs on large spreadsheets, Writer documents, small documents, if only one document is open, or if more than one is open. When it becomes unresponsive, I cannot do anything--I cannot navigate within the document, I cannot open a new document, nothing. It I attempt to close the document, after about half a minute a box will come up: "The window (document name) does not seem to be responding . . . do you want to force it to terminate?" If I choose terminate, after about half a minute, all Open Office documents will close. All unsaved work will be lost. Upon re-opening any Open Office document, the document recovery page will come up. Crash frequency is perhaps every hour or so.

Here is what I know and what I have tried:

Reset user profile (no change).

At one point, found 3 soffice.bin processes running in Task Manager (information found in a post on here). Have not seen multiple soffice.bin processes running in Task Manager since disabling quick starter, but Open Office still hangs.

Disabled "use a Java runtime environment." Java is not installed and I don't think I need it for my applications (Calc and Writer.) Did not change anything.

No significant CPU activity when it hangs (2%). Other programs on computer remain useful, and non-Open Office files accessible.

These are fresh, bare-bones installations of Lubuntu 64-bit. These are business computers; I run Apache Open Office, Dropbox, Firefox, a pdf reader that comes with Lubuntu, and an HP laser printer. That's all!

AOO 4.1.5 32-bit runs fine in an old Gateway Pentium 4, Lubuntu 14.04.
AOO 4.1.2 32-bit runs fine in an old Dell D600 Pentium M, Lubuntu 14.04.
AOO 4.1.5 32-bit runs fine in an old Panasonic CF-29 Pentium M, Lubuntu 14.04.

AOO 4.1.6 64-bit previously ran fine in the same Dell T1700 that is currently giving trouble, but it was running under Ubuntu 18.04 64-bit LTS with a Mate destop. (I wiped the hard drive and reloaded because the operating system became unstable for some reason).

AOO 4.1.5 64-bit also crashes in the Windows 7 partition of the Panasonic CF-31, but I am not sure it is the same problem. The crashes are more frequent and seem to take a slightly different form. That is a separate issue that doesn't need to be solved right now.
I am starting to wonder if this is actually an issue between Lubuntu 18.04 64-bit LTS and AOO, but I don't know how to begin to troubleshoot.

I have extensive accounting and invoicing documents, mostly Calc, that I have developed for my small business. Perhaps hundreds of hours of work are represented here. I have been extremely satisfied until these latest loads on newer computers have failed to work. Migrating to a different software system would be difficult and time-consuming (have tried LibreOffice, and it is not fully compatible with my formulas). Any troubleshooting advice appreciated. Thanks!
Last edited by sampsonrepair on Sat Jan 25, 2020 1:15 am, edited 4 times in total.
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu

Postby RoryOF » Thu Jan 02, 2020 6:19 pm

Try running a system monitor and see if OpenOffice is eating up all the memory.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31541
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Thu Jan 02, 2020 6:23 pm

If I am interpreting Lubuntu Task Manager correctly, that does not seem to be the case. Thank you for the reply.
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu

Postby RoryOF » Thu Jan 02, 2020 6:25 pm

My Calc use with OpenOffice is trivial; I run OO 4.1.7 on Xubuntu (4 and 8 GB machines) and find it quite stable.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31541
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Thu Jan 02, 2020 7:52 pm

So AOO is hung as I write. Have been doing inventory (spreadsheet entries) for the past hour or so. I am actually on the forum with the "hung" computer, so everything else is working fine. Total memory used is 1617 MB out of 7860 available. 333.4 of that is being used by the single soffice.bin process. For whatever it's worth, the virtual desktop I am running AOO on (Lubuntu allows the operation of multiply "virtual destops"), is unusable. I thought it was strictly a Calc issue, but had Writer hang last week too.

I agree, AOO has been quite stable for me over the past ten years. I'm not even sure this is truly an AOO issue, but I'm confused as far as where to go next.
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu

Postby Villeroy » Thu Jan 02, 2020 7:56 pm

(have tried LibreOffice, and it is not fully compatible with my formulas)

Would you please tell us the spreadsheet formulas that return wrong results or errors with LibreOffice? OpenOffice has died and using Linux you should always prefer the software that is shipped with your distribution. Since many years OpenOffice is not shipped with any distribution. I would think that there are good reasons and in fact most of my documents having database forms don't work with OpenOffice on Linux.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28651
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Apache OpenOffice hanging in Lubuntu

Postby RoryOF » Thu Jan 02, 2020 7:58 pm

Before you installed OpenOffice, did you purge LibreOffice? I don't think Lubuntu comes with LibreOffice pre-installed, but just in case, try in a terminal

Code: Select all   Expand viewCollapse view
sudo apt purge libreoffice*


Don''t omit the trailing * in that command line.

If that doesn't help you could try Xubuntu 18.04 - make a live USB key, with persistence (that will remember you have installed OpenOffice on it) and try that.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31541
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Thu Jan 02, 2020 8:07 pm

Correct, Lubuntu comes with Abiword and Gnumeric installed. I did try sudo apt purge libreoffice* already, no joy. I wonder if there could possibly be an interference between Abiword / Gnumeric and AOO? Have not run into this with previous Lubuntu installations, but maybe something has changed. I may try purging Abiword and Gnumeric and seeing what happens. Failing that, I may try Xubuntu 18.04. Glad to hear Xubuntu is working well for you.
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu

Postby RoryOF » Thu Jan 02, 2020 8:21 pm

I doubt there is any interference between AbiWord/Gnumeric and OpenOffice. I have AbiWord installed on my main Xubuntu machine - it opens some docx files that OO won't (Only for for Forum faultfinding - I use .odt always).

I found Xubuntu more to my liking than that of lubuntu.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31541
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Apache OpenOffice hanging in Lubuntu

Postby keme » Thu Jan 02, 2020 8:31 pm

Just a thought: As an emergency workaround you could also try running the Windows AOO on Wine. It will be an otherworldly experience, clunky but reliable, and you may feel a little silly for doing it.

I would not go there if you haven't used Wine before. The Windows-like environment takes some time to set up to workable condition. Interaction with other apps is not seamless. If you have done it before, it may be less work than setting up another OS install. If you haven't, go with RoryOF's suggestion.
User avatar
keme
Volunteer
 
Posts: 3383
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Apache OpenOffice hanging in Lubuntu

Postby Villeroy » Fri Jan 03, 2020 6:58 pm

In 30 years I have never seen any accounting on spreadsheets that actually works. I would be interested to see what you have with some dummy data in it. And I would be interested in formulas that work in OpenOffice but not in LibreOffice.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28651
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Sat Jan 04, 2020 12:41 am

Villeroy, thanks for the input. Attached is a spreadsheet I use in my business for customer invoicing purposes. This is stored as a template, and is very easy to open and fill out as necessary, print, and then file as a .pdf for a permanent record. The "customer" field will not work properly for you because it is linked to another spreadsheet on my computer that serves as an information database.
The formulas in this spreadsheet will return all the correct values when opened with Libreoffice (tested yesterday). However, Column G is a mess unless every field is filled out completely. There may be a better way to do what I'm doing here, but for me the general formula format =IF(E16;E16*F16;"") has worked well for over nine years now. AOO interprets this as "If there is a value in E16, then multiply the value in E16 by the value in F16 and return the product here. Otherwise, leave this cell blank." I realize the cell is not technically "blank" as there is a formula in it, but for practical, everyday purposes, it is blank--no zeros, no error codes, nothing.
I realize AOO has been frowned upon by the Linux community for some years now, but since it has always worked for me (until about a month ago), I have always gone to the trouble to load it into all my Lubuntu installs.
I would be happy to send you a copy of my accounting spreadsheet, but it will likely be next week before I can do it. The spreadsheet is very large and it is going to take a little while to fill it with enough meaningful data that you can see how it works. My Internet connection at home on the weekend is not good enough to send it until I get back in my office next week.
I have a friend who ran a business website on spreadsheets (AOO!) for many years. I have no idea how anyone could do that.
Thank you for your help!
Attachments
test document1.ods
(29.53 KiB) Downloaded 46 times
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Sat Jan 04, 2020 12:45 am

That appears to have attached as "read only?" Is there a way to attach it so that you can see everything and play with the formulas, etc?
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu

Postby Villeroy » Sat Jan 04, 2020 2:14 am

And which formula fails with LibreOffice? All I see is that your VLOOKUP formulas are wrong, even if they happen to return the expected result, they are wrong. You should add the 4th argument as 0.

This is a lousy input form for a single invoice. It has nothing to do with accounting.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28651
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Sat Jan 04, 2020 6:27 am

Good, you can see my formulas. It opens as 'read only' when I click my own post.

You are correct. That spreadsheet has nothing to do with accounting. It is a customer's copy of a repair invoice.

I will be happy to post my book keeping spreadsheet next week, if you don't think it will drag this too far off topic?

"Your fourth argument should be 0". You are referring to the "" argument? Zero works, mathematically. But it returns a value of 0 (zero), rather than a blank cell. The result is quite visually unappealing. In my experience, LibreOffice does not know how to interpret the "" argument. I am sure LibreOffice is excellent software. But it tends to render my spreadsheets a cluttered mess. Since AOO has worked perfectly to this point, I have not tried to make them compatible with LO.

"Your VLOOKUP formula is wrong."

Please elaborate. The format in the invoice I attached has worked perfectly for me for many years. If there is a better or easier way to do the VLOOKUP function in the future, I am pleased to learn. I am sure you noticed there is a data validity function on $E3, referenced to a cell range in a different spreadsheet? This creates a drop- down menu which will not work for you.

I am sorry you feel a spreadsheet is a lousy way to write a customer invoice. It works well for me and no one has ever complained about it.

Thanks again for your input.
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu

Postby robleyd » Sat Jan 04, 2020 7:05 am

Villeroy wrote:your VLOOKUP formulas are wrong, even if they happen to return the expected result, they are wrong. You should add the 4th argument as 0


Villeroy is saying that the fourth argument to the VLOOKUP function should be zero
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3508
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Apache OpenOffice hanging in Lubuntu

Postby Zizi64 » Sat Jan 04, 2020 9:31 am

Migrating to a different software system would be difficult and time-consuming (have tried LibreOffice, and it is not fully compatible with my formulas).


Code: Select all   Expand viewCollapse view
=IF(E20;E20*F20;"")

The column E and F contain WHITE SPACE characters.

Theerefore the result of the formula
Code: Select all   Expand viewCollapse view
=IF(E20;...

will be an error message: #VALUE!
in the LibreOffice.
VALUEmessage.png

...And maybe that is the reason why the document crashes in my Apache OpenOffice 4.1.5 Portable, and why it can open in my LibreOffice 6.2.8 - with many error messages.

Use really empty cells, and the
Code: Select all   Expand viewCollapse view
IF(ISBLANK(E20);...

function to check it if a data is present in column E.

Or check it if the E20 contains a WHITESPACE or not:
Code: Select all   Expand viewCollapse view
IF(E20=" ";...


Or check it if the numeric value of the E20 equals zero or not:
Code: Select all   Expand viewCollapse view
IF(E20=0;...


Or check it if the E20 contains a NUMERIC VALUE or not:
Code: Select all   Expand viewCollapse view
IF(ISNUMBER(E20);...
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9593
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Sat Jan 04, 2020 7:16 pm

We are way off the topic we started with, but I am learning new things so it is good. I revised the IF formulas (which are supposed to return a blank cell if false and perform a mathematical calculation if true) to the general format of =IF(ISBLANK(E16);"";E16*F16). Works well as long as the "white spaces" are all deleted with the backspace key. Also added the zero fourth argument to the VLOOKUP formulas, which works just the same except it eliminates the need to have all my data sorted in strict ascending order to avoid erroneous output.

It would be wonderful to eventually have all my spreadsheets compatible with all major spreadsheet software. I have felt locked into AOO primarily because the way it has handled my IF arguments seems unique in the spreadsheet world.

As for the original issue, AOO seems to be stable in Xubuntu. Xubuntu itself is another matter and I am not sure why. I did not format the partition I installed it onto, just replaced Lubuntu with Xubuntu. Perhaps that is the reason. But that is a different topic.

Thank you all for the advice.
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu

Postby RoryOF » Sat Jan 04, 2020 7:39 pm

Note that linux does not really like NTFS, although it will run on it and copy to it.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31541
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Mon Jan 06, 2020 4:21 pm

I'm quite certain the file system on the partition I installed Xubuntu on was EXT4, or something like that. I'm not sure why I am having issues, except perhaps because Xubuntu seems to have installed over the existing Lubuntu installation. Such an installation never seems to work as well as a totally clean installation on a formatted hard drive. I ran Xubuntu a few years back and it worked really well. Issues include the system doesn't see USB flash drives, wireless driver is missing, and printer driver disappeared after working correctly for a day. I will keep working at it . . . I know this is not a Linux forum!
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Mon Jan 06, 2020 4:29 pm

Villeroy: My accounting spreadsheet is too large to upload to the forum. If you are still interested, PM me privately at the email listed at the top of the invoice document I attached on Friday and I will be glad to send it to you.
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu

Postby RoryOF » Mon Jan 06, 2020 4:58 pm

sampsonrepair wrote: I ran Xubuntu a few years back and it worked really well. Issues include the system doesn't see USB flash drives, wireless driver is missing, and printer driver disappeared after working correctly for a day. I will keep working at it . . . I know this is not a Linux forum!


In the event that the computer doesn't see a USB flash drive, try rebooting with the USB key installed. I recently purchased a WiFi card - Netis brand, rather than Tp-link (which I prefer) - but I had to take what they had because of time constraints. It came with a driver disk, both Windows and linux. I didn't have time to sort the linux drivers; instead, I installed hardinfo from the Software center. It detected the card and automatically installed the drivers for it.

I've had no problems with printer drivers vanishing (4 printers online); normally on linux one just needs to plug in a printer, turn it on, and it will install.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31541
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Thu Jan 09, 2020 1:42 am

Thanks, I really feel that the Xubuntu issues somehow lie with my installation and not with the operating system itself. I tried live CD today, and USB flash drive works just fine, also wireless Internet works just fine. My HP P1006 printer has never worked by just plugging in. The process to get it to work has always been quite involved. That is why I was surprised to see the printer driver survived the upgrade from Lubuntu to Xubuntu. I do not know why it no longer sees the printer. Unfortunately, I need to do "real work" this week and don't have much time to work on the computer issues. So I am doing all my business work on the old, slow, reliable computers.
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: Apache OpenOffice hanging in Lubuntu [solved]

Postby sampsonrepair » Sat Jan 25, 2020 1:23 am

I am going to call this one solved. I have had AOO 4.1.5 running on my Panasonic CF-31 Toughbook notebook computer in a Xubuntu 18.04 LTS operating system for about ten days now with no issues whatsoever, even when working with AOO for several hours straight. This was not possible previously. I do not know why AOO kept crashing in Lubuntu 18.04, but I am convinced that Open Office did not like something in the Lubuntu desktop, or something with other software in the installation. It would take someone a lot smarter than I to figure out what the issue was. As I suspected, re-formatting my Linux partition and completely reinstalling Xubuntu eliminated the strange issues I was experiencing with Xubuntu itself.

Thanks to you all, I have also learned some better ways to write my formulas. Now for the long task to update all my templates!
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: [Solved] Apache OpenOffice hanging in Lubuntu

Postby Villeroy » Sat Jan 25, 2020 3:59 pm

Nevertheless, your VLOOKUP formulas are plain wrong and LO does the right thing when boolean evaluation of white space returns #VALUE.

OpenOffice Calc:
=IF("x") => #VALUE!
=IF(" ") => FALSE
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28651
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Sat Jan 25, 2020 6:31 pm

Villeroy, thanks again for the input. I'm not sure if you have followed this whole thread or not, but I do recognize, thanks mostly to your input, that some of my formulas are written very poorly. I especially appreciate the VLOOKUP input, as I now know how to avoid the erroneous output I sometimes received from the VLOOKUP function.

Re-writing my IF statements as suggested in this thread appears to resolve the apparent incompatibility between my documents and LibreOffice Calc.

I have searched these forums enough to realize you are an expert in spreadsheets, so I am sure you can appreciate that for someone like me who has learned much of what little I know by trial and error, it is going to take me awhile to redo everything. So I'm not really sure how to interpret what seems to be a condescending tone in your post. Maybe you don't mean it that way at all.

Anyway, I am glad I posted my problem. I have learned a lot in the process. Thank you again to all who took the time to respond. God bless you all.
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: [Solved] Apache OpenOffice hanging in Lubuntu

Postby Villeroy » Sat Jan 25, 2020 6:45 pm

The "errorneous output" of VLOOKUP indicates that YOU made a mistake, i.e. that the search value does not exactly match any value in the lookup range. The way you use VLOOKUP will give you wrong matches. This is fine as long as the wrong matches happen to be the intended matches. This way you give up control. You can not trust your own program anymore.

The problem with IF(E16) is that you enter spaces into cells where you actually want to clear them. This means that those cells have a value while they are looking empty. Simply use the backspace key and IF(E16) will return False in case of zero or empty cell. Unfortunately, LO mimics Excel where Backspace and Del are switched but there is a compatibility setting in the Calc options which restore the OpenOffice defaults for these keys.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28651
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Sun Jan 26, 2020 1:17 am

I cannot tell if you are talking about the incorrect formulas in the spreadsheet I posted to the forum or if you are saying the VLOOKUP correction as suggested by robleyd is still wrong. The format =VLOOKUP(A8;'data location'#$Customers.$A$1:$E$1000;2) is the general format I have used for a long time, and I now realize that this is WRONG. It has always worked provided my data is kept in strict alphabetical order, but get the data out of order or enter a value not in the table, and this fourmula produces errors. MY FAULT.

My corrected formula is =VLOOKUP(A8;'data location'#$Customers.$A$1:$E$1000;2;0) which seems to solve the issues of erroneous outputs. I still get a #NA output if the referenced cell (A8) is blank or has a value entered that is not in my lookup table, but that does not create a problem for me. Do you see any potential problems with the corrected formula?

I think I understand about the white space issue, it explains why I have had issues with blank-appearing cells producing unexpected results when referenced by a formula. I understand about using backspace to clear these cells.

My formulas =IF(E16;E16*F16;"") and ='sheet reference'.a1 are simply poor formulas. They tend to return zero values when I would prefer the cell to appear blank. I am learning they are supposed to return zero values, and I think I understand why. Using the =IF(ISBLANK(E16);"";E16*F16) format as suggested by Zizi64 seems much better. Unfortunately, I have thousands of cells in which these poor IF arguments need to be changed. It is going to take time.

Am I missing anything here?

Thank you!
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm

Re: [Solved] Apache OpenOffice hanging in Lubuntu

Postby Zizi64 » Sun Jan 26, 2020 9:44 am

Using the =IF(ISBLANK(E16);"";E16*F16) format as suggested by Zizi64 seems much better. Unfortunately, I have thousands of cells in which these poor IF arguments need to be changed. It is going to take time.


You can try to do it faster by the Search&Replace function

Select the relevant column...
Search:
Code: Select all   Expand viewCollapse view
=IF(E

Replace:
Code: Select all   Expand viewCollapse view
=IF(ISBLANK(E

Option: In the selected cell range only
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9593
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Apache OpenOffice hanging in Lubuntu

Postby sampsonrepair » Sun Jan 26, 2020 2:36 pm

I will try that. I have, of course, used the "paste special" function many times; but your way may be faster. I should have read the book ten years ago when I had more time. It would likely have saved time in the long run.
Thanks!
Apache OpenOffice 4.1.4 64-bit; Lubuntu 18.04.3 LTS
sampsonrepair
 
Posts: 15
Joined: Thu Jan 02, 2020 5:19 pm


Return to Linux

Who is online

Users browsing this forum: No registered users and 1 guest