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

On 21/06/2015 02:31 PM, 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
I too am running 4.3.7.2, but on what is known as Curtains, though you 
may still think of windows.

In my instance, blanks are interpreted as zeros, so your problem doesn't 
appear here.

However, a long-term annoyance to me is LO interpreting alpha chars as 
illicit in some formulae.

That was done in apparent slavish adherence to micro$$ bad practice; 
good old 123, and Mosaic Twin, took such chars to be zero.

Using your example on my system, if D10 = something possibly useful, 
such as "insert revenue here", the dreaded #VALUE! will appear.

You could try, z.b.,

=IF(sum(D10)-sum(K10)>0,sum(D10)-sum(K10),"")

For no apparent reason, that works to interpret alphas as zero, and so might work for you.

This example is unnecessarily verbose, just to illustrate the idea.

Other such redundant function employments have worked in other places, so you could tinker with the 
general notion.

trj


--
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