Re: [libreoffice-users] Ignoring text in calc formula

Toni

Please advise which LO version you are using and your OS. This will help 
us determine if the problem is a know issue with a specific version of 
LO or with a particular OS.

For your problem, I was able to replicate the behavior in LO 3.4.4 and 
try the following:

=sum(if(isnumber(b5),-b5,0),if(isnumber(c5),-c5,0),if(isnumber(d5),d5,0,if(isnumber(e5),e5,0))

Alternate

=sum(if(isnumber(d5),d5,0),if(isnumber(e5),e5,0))-sum(if(isnumber(b5),b5,0),if(isnumber(c5),c5,0))

I am validating that the data is actually a number (isnumber(cell) = 
true) and using either the value in the cell if true or 0 if it is not a 
number. Without redesigning your entire spreadsheet this may be the 
simplest way to fix your problem.

On 01/01/2012 12:23 PM, Toni & Chris PC4 wrote:
> Hello,
>
> I use this formula in calc on a large forecast spreadsheet
>
> =sum(e4+d5)-(b5+c5)
>
> There has to be some cells with text but the text is not being ignored
>
> When it gets to a row with text in a cell it shows   #VALUE!
>
> I have searched and =sum is supposed to fix this ??
>
> Can you help.
>
> Thank you
>
> Toni


--
Jay Lozier
jslozier@gmail.com


--
For unsubscribe instructions e-mail to: users+help@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