Preventing the display of #VALUE! in a spreadsheet

classic Classic list List threaded Threaded
8 messages Options
pete nikolic pete nikolic
Reply | Threaded
Open this post in threaded view
|

Preventing the display of #VALUE! in a spreadsheet

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

What is the trick to preventing the display of aforementioned  string

Thanks  

Pete .


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

--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Preventing the display of #VALUE! in a spreadsheet

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


--
To unsubscribe e-mail to: [hidden email]
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

marianne-x marianne-x
Reply | Threaded
Open this post in threaded view
|

Re: Preventing the display of #VALUE! in a spreadsheet

In reply to this post by pete nikolic
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: [hidden email]
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

pete nikolic pete nikolic
Reply | Threaded
Open this post in threaded view
|

Re: Preventing the display of #VALUE! in a spreadsheet

In reply to this post by pete nikolic
On Sun, 21 Jun 2015 20:05:33 +0100
Brian Barker <[hidden email]> 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: [hidden email]
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

m.a.riosv m.a.riosv
Reply | Threaded
Open this post in threaded view
|

Re: Preventing the display of #VALUE! in a spreadsheet

In reply to this post by pete nikolic
There are a couple of options about how to treat strings in formulas.

Menu/Tools/Options/LibreOffice calc/Formula - Detailed calculation settings - Custom

Miguel Ángel.
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Preventing the display of #VALUE! in a spreadsheet

In reply to this post by pete nikolic
At 21:11 21/06/2015 +0100, Pete Nikolic 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.
>
>I have managed to get round it right now  using
>=IF(COUNT(D10-K10)=2,D10-K10,"")
>That seems to do the job ...

I fancy you mean
=IF(COUNT(D10,K10)=2,D10-K10,"")
- with a comma, not a minus sign.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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

Regina Henschel Regina Henschel
Reply | Threaded
Open this post in threaded view
|

Re: Preventing the display of #VALUE! in a spreadsheet

In reply to this post by pete nikolic
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: [hidden email]
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
pete nikolic pete nikolic
Reply | Threaded
Open this post in threaded view
|

Re: Preventing the display of #VALUE! in a spreadsheet

In reply to this post by pete nikolic
On Sun, 21 Jun 2015 21:52:53 +0100
Brian Barker <[hidden email]> wrote:

> At 21:11 21/06/2015 +0100, Pete Nikolic 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.
> >
> >I have managed to get round it right now  using
> >=IF(COUNT(D10-K10)=2,D10-K10,"")
> >That seems to do the job ...
>
> I fancy you mean
> =IF(COUNT(D10,K10)=2,D10-K10,"")
> - with a comma, not a minus sign.
>
> Brian Barker
>
>

Yep you got it   typo on my part ...


Pete .


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

--
To unsubscribe e-mail to: [hidden email]
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