Re: [libreoffice-users] Preventing the display of #VALUE! in a spreadsheet

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