Document Foundation Mail Archive › LibreOffice › Users

Hiding negative numbers in Clac?

Classic

List

Threaded

9 messages Options

Johnny

Hiding negative numbers in Clac?

I have a spreadsheet where one cell is defined by several others with a
subtraction of a number hard coded in there. The cell displays the negative
number when all other cells are blank. Is there a way to not display negative
numbers in a cell? The outcome of the cells will never be negative, once the
proper values are entered, so that won't be a problem.

--
For unsubscribe instructions 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

JOE CONNER

Re: Hiding negative numbers in Clac?

On 5/23/2012 02:15 PM, Johnny wrote:
> I have a spreadsheet where one cell is defined by several others with a
> subtraction of a number hard coded in there. The cell displays the negative
> number when all other cells are blank. Is there a way to not display negative
> numbers in a cell? The outcome of the cells will never be negative, once the
> proper values are entered, so that won't be a problem.
>
>
>
You want the ABS function:

=ABS(-56) returns 56.

=ABS(12) returns 12.

=ABS(0) returns 0.

Joseph E Conner
Poulsbo, WA USA

--
For unsubscribe instructions 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

Jay Lozier

Re: Hiding negative numbers in Clac?

In reply to this post by Johnny

On 05/23/2012 05:15 PM, Johnny wrote:
> I have a spreadsheet where one cell is defined by several others with a
> subtraction of a number hard coded in there. The cell displays the negative
> number when all other cells are blank. Is there a way to not display negative
> numbers in a cell? The outcome of the cells will never be negative, once the
> proper values are entered, so that won't be a problem.
>
>
>
In the cell try this

=if(expression>=0,expression,""), where expression is the
expression/function already in the cell. As written the if statement
will put 0 or positive number in the cell and an empty string in the
cell if it is negative.

=if(expression>0, expression,"") will only put a value in the cell if
the expression >0, otherwise it places an empty string.

--
Jay Lozier
[hidden email]

--
For unsubscribe instructions 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

Johnny

Re: Hiding negative numbers in Clac?

In reply to this post by JOE CONNER

On Wednesday, May 23, 2012 04:06:16 PM JOE CONNER wrote:

> On 5/23/2012 02:15 PM, Johnny wrote:
> > I have a spreadsheet where one cell is defined by several others with a
> > subtraction of a number hard coded in there. The cell displays the
> > negative number when all other cells are blank. Is there a way to not
> > display negative numbers in a cell? The outcome of the cells will never
> > be negative, once the proper values are entered, so that won't be a
> > problem.
>
> You want the ABS function:
>
> =ABS(-56) returns 56.
>
> =ABS(12) returns 12.
>
> =ABS(0) returns 0.
>
>
>
> Joseph E Conner
> Poulsbo, WA USA

I want nothing displayed if the other cells have nothing in them.

A1 = 10
A2 = 20
A3 = 30
A4 = sum(A1 + A2 + A3 - 5)

Prior to entering data into A1 thru A3, A4 will display -5. I want A4 to be
blank, or at least appear so until something is entered into A1,2 or 3 that
will bring A4's value >= 0.

--
For unsubscribe instructions 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

Jay Lozier

Re: Hiding negative numbers in Clac?

On 05/23/2012 07:38 PM, Johnny wrote:

> On Wednesday, May 23, 2012 04:06:16 PM JOE CONNER wrote:
>> On 5/23/2012 02:15 PM, Johnny wrote:
>>> I have a spreadsheet where one cell is defined by several others with a
>>> subtraction of a number hard coded in there. The cell displays the
>>> negative number when all other cells are blank. Is there a way to not
>>> display negative numbers in a cell? The outcome of the cells will never
>>> be negative, once the proper values are entered, so that won't be a
>>> problem.
>> You want the ABS function:
>>
>> =ABS(-56) returns 56.
>>
>> =ABS(12) returns 12.
>>
>> =ABS(0) returns 0.
>>
>>
>>
>> Joseph E Conner
>> Poulsbo, WA USA
> I want nothing displayed if the other cells have nothing in them.
>
> A1 = 10
> A2 = 20
> A3 = 30
> A4 = sum(A1 + A2 + A3 - 5)
>
> Prior to entering data into A1 thru A3, A4 will display -5. I want A4 to be
> blank, or at least appear so until something is entered into A1,2 or 3 that
> will bring A4's value >= 0.
>
>

in A4 =if(sum(A1:A3,-5)>0,sum(A1:A3,-5),"")

--
Jay Lozier
[hidden email]

--
For unsubscribe instructions 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

Johnny

Re: Hiding negative numbers in Clac?

On Wednesday, May 23, 2012 07:48:33 PM Jay Lozier wrote:

> On 05/23/2012 07:38 PM, Johnny wrote:
> > On Wednesday, May 23, 2012 04:06:16 PM JOE CONNER wrote:
> >> On 5/23/2012 02:15 PM, Johnny wrote:
> >>> I have a spreadsheet where one cell is defined by several others with a
> >>> subtraction of a number hard coded in there. The cell displays the
> >>> negative number when all other cells are blank. Is there a way to not
> >>> display negative numbers in a cell? The outcome of the cells will never
> >>> be negative, once the proper values are entered, so that won't be a
> >>> problem.
> >>
> >> You want the ABS function:
> >>
> >> =ABS(-56) returns 56.
> >>
> >> =ABS(12) returns 12.
> >>
> >> =ABS(0) returns 0.
> >>
> >>
> >>
> >> Joseph E Conner
> >> Poulsbo, WA USA
> >
> > I want nothing displayed if the other cells have nothing in them.
> >
> > A1 = 10
> > A2 = 20
> > A3 = 30
> > A4 = sum(A1 + A2 + A3 - 5)
> >
> > Prior to entering data into A1 thru A3, A4 will display -5. I want A4 to
> > be blank, or at least appear so until something is entered into A1,2 or 3
> > that will bring A4's value >= 0.
>
> in A4 =if(sum(A1:A3,-5)>0,sum(A1:A3,-5),"")

Thanks Jay, that did the trick.

--
For unsubscribe instructions 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

Tom

Re: Hiding negative numbers in Clac?

In reply to this post by JOE CONNER

Hi :)
I really prefer the mailing-list / forums method of user-support. Often people are unclear or mis-state what they want and don't understand even what the problem is sometimes. Tech support from a single person, for example telephone support, fails to give the range of different ideas that a list or forum generates.

Good work all! :)
Regards from
Tom :)

--- On Thu, 24/5/12, JOE CONNER <[hidden email]> wrote:

From: JOE CONNER <[hidden email]>
Subject: Re: [libreoffice-users] Hiding negative numbers in Clac?
To: [hidden email]
Date: Thursday, 24 May, 2012, 0:06

On 5/23/2012 02:15 PM, Johnny wrote:
> I have a spreadsheet where one cell is defined by several others with a
> subtraction of a number hard coded in there. The cell displays the negative
> number when all other cells are blank. Is there a way to not display negative
> numbers in a cell? The outcome of the cells will never be negative, once the
> proper values are entered, so that won't be a problem.
>
>
>
You want the ABS function:

=ABS(-56) returns 56.

=ABS(12) returns 12.

=ABS(0) returns 0.

Joseph E Conner
Poulsbo, WA USA

--
For unsubscribe instructions 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

--
For unsubscribe instructions 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

Re: Hiding negative numbers in Clac?

In reply to this post by Johnny

Hi Johnny,

Johnny schrieb:
[..]

> I want nothing displayed if the other cells have nothing in them.
>
> A1 = 10
> A2 = 20
> A3 = 30
> A4 = sum(A1 + A2 + A3 - 5)
>
> Prior to entering data into A1 thru A3, A4 will display -5. I want A4 to be
> blank, or at least appear so until something is entered into A1,2 or 3 that
> will bring A4's value>= 0.
>
>

If you really want only change the display, then use the format code
Standard;"";Standard
as cell format.

Kind regards
Regina

--
For unsubscribe instructions 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

Andreas Säger

Re: Hiding negative numbers in Clac?

Am 24.05.2012 18:11, Regina Henschel wrote:

>
> If you really want only change the display, then use the format code
> Standard;"";Standard
> as cell format.

which is

General;"";General

in English locale context.

--
For unsubscribe instructions 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