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

Hi Pete,

pete nikolic schrieb:
> Hi All ..
>
>
> Having a problem here that is becoming a real PITA situation .
>
> 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!  .

If the cells are really empty, that should not happen. I guess, that the 
cells are not empty, but contain a "Blank"-character or an empty string. 
In that case Calc tells you, that it cannot calculate with text.

> What is the trick to preventing the display of aforementioned  string

You can force the cell entries to be treated as numbers in a way, that 
text is treated a zero. That is done automatically if you use functions 
with an array as parameter oder explicitly with the function N.

=IF( N(D10)-N(K10)>0; N(D10)-N(K10);"")

For me the delimiter is a semicolon, I'm not sure about your comma as 
delimiter.

Kind regards
Regina




> Thanks
>
> Pete .


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