On Sun, 21 Jun 2015 20:05:33 +0100 Brian Barker <b.m.bar...@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+unsubscr...@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
