Date: prev next · Thread: first prev next last
2015 Archives by date, by thread · List index


On Sun, 21 Jun 2015 20:05:33 +0100
Brian Barker <b.m.barker@btinternet.com> wrote:

At 19:31 21/06/2015 +0100, Pete Nikolic wrote:
I am running Libre office Version 4.3.7.2 on Arch Linux. I have a 
lot of cells with this or similar in
=IF(D10-K10>0,D10-K10,"")
the problem is that if either D10 or K10 is blank the result is 
displayed as #VALUE! that means several hundred times not only is it 
a pain but it mucks the printing up as well the results do not look 
good covered in #VALUE! . What is the trick to preventing the 
display of aforementioned string.

o You shouldn't really think of suppressing the error message, but 
instead of avoiding the error. (But that may be what you mean.) So 
you could check the result of your formula using ISERR() or ISERROR() 
and replace the message with whatever you prefer - possibly blank or 
null. But I don't recommend this.

o How about correcting your data? If you want to do calculations on 
it, the data really shouldn't have text values of any sort. (Are you 
sure that blank cells are not automatically interpreted as zeroes in 
a calculation?) It is quite easy to edit a spreadsheet to correct 
problems such as this, even if there are hundreds of suspect values. 
You could either modify the cell contents or simply create an 
additional column or row containing the modified values.

o Alternatively, you can test for the presence of a genuine number 
using the ISNUMBER() function. So
=IF(AND(ISNUMBER(D10),ISNUMBER(K10),D10>K10),D10-K10,"")
should do what you need.

I trust this helps.

Brian Barker



HI ok .


I will test that out ..

I have managed to get round it right now  using
=IF(COUNT(D10-K10)=2,D10-K10,"")  

That seems to do the job and i cant see any ill effects on the way it works  you may
have an improvement  ....



Thanks  

Pete .



-- 
Illegitimi non carborundum . ro for the purists out there
Noli nothis permittere te terere. 

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.