OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How can I make a negative answer return a value of 0?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
member
General User
General User


Joined: 25 Dec 2005
Posts: 30

PostPosted: Tue May 01, 2007 6:34 am    Post subject: How can I make a negative answer return a value of 0? Reply with quote

I apologize for not doing a search first, but I don't know what to search for. I need to have a number that is actually a negative to show 0 instead of (for example) -531.00 needs to be 0.
I can figure out most formulas by trial and error, but I don't know how to do this.

In this case I am using the formula =B70-C70-D70-E70-F70-G70-H70-I70-J70-K70-L70.

This is giving me a negative, but I need it to just be 0. Can anyone tell me what to do?

Thanks!
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

PostPosted: Tue May 01, 2007 6:53 am    Post subject: Reply with quote

Quote:
=IF( B70-C70-D70-E70-F70-G70-H70-I70-J70-K70-L70 > 0; B70-C70-D70-E70-F70-G70-H70-I70-J70-K70-L70; 0)

Which you can simplify a bit to:
Quote:
=IF( B70-SUM(C70:L70) > 0; B70-SUM(C70:L70); 0 )

And you can simplify even more if you can use a helper cell somewhere to store the intermediate result instead of repeating it.
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Tue May 01, 2007 6:55 am    Post subject: Reply with quote

OOo allows you to define a number format. The form is Positive;Negative;Zero so try
#,###.00;"0";"Zero"
just to see how it works.
Back to top
View user's profile Send private message
member
General User
General User


Joined: 25 Dec 2005
Posts: 30

PostPosted: Tue May 01, 2007 7:10 am    Post subject: Reply with quote

Thanks Acknak. That works, I will have to spend some time learning why it works, but I
will want to learn so I can use it in the future. You saved me a lot of time.

JohnV, thank you too, but you pretty much lost me. I'm not ungrateful, just confused.
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

PostPosted: Tue May 01, 2007 7:15 am    Post subject: Reply with quote

Yow! Nice idea.

Just don't use the value in other calculations and expect it to be zero. The cell will still have the negative number value, even though it displays "0".
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Tue May 01, 2007 7:21 am    Post subject: Reply with quote

member,

Select a column heading or a group of cells, do Format > Cell > Number tab, select Number and enter the format code in that box. Positive numbers will have a comma separator and 2 decimal places, negatives will be the text 0, and zeros will be the text Zero.

In your case you may prefer the format code
#,###.00;"0";""
so true zero values are not shown at all.
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

PostPosted: Tue May 01, 2007 7:30 am    Post subject: Reply with quote

Quote:
I will have to spend some time learning why it works...

Just ask if you get stuck.

The "IF" is a little hard to see, but it's just a two-way branch:
Quote:
IF( E; X; Y)

If E is true, then the result is X,
If E is false, then the result is Y.

Each of the three parts is allowed to be an expression, which can be anything from a simple number, to a whole calculation.

For your problem, it looks like this:
Quote:
IF( calculation > 0;
___then the result is calculation;
___otherwise the result is 0)
Back to top
View user's profile Send private message
member
General User
General User


Joined: 25 Dec 2005
Posts: 30

PostPosted: Tue May 01, 2007 8:45 am    Post subject: Reply with quote

JohnV, To be honest I have never noticed the format code box. I will try to read up on using that function. I am using these numbers for other calculations, so I can only use your example if I don't need to make more calculations with that number. I do think I might be able to use the information in some other cases. Before now, I didn't even know it was an option. Thanks

I do visit Format > Cell > Number tab a lot because it I am always having to change my numbers to two decimal places. I can't seem to get it to default to being 2 decimals.




Acknak

If for example p71 totals to a negative (say -300) and Q71 is this formula =P71*0.9235*0.153 for an answer of -42.39, how would I make this (Q71) a zero (while leaving p71 as a negative?)




Another Question

If I were trying to shorten the formula =B70-C70-D70-E70-F70-G70-H70-I70-J70-K70-L70, and wanted the negative answer how would I do that? I tried ( B70-SUM(C70:L70)) but that didn't work.

I appreciate all of this, I usually have to do things the hard way, because it is the only way I managed to figure out.


****************
I can't even work with the database at all, I used works database before and Open Office is way over my head. I pretty much had to use the report function to do formulas in works database if I wanted to do math. Thats another problem all together, I don't expect anyone to try to help me deal with database issues right now. Again though thanks for any and all help with the calc issues.
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

PostPosted: Tue May 01, 2007 9:01 am    Post subject: Reply with quote

Quote:
...Q71 is this formula =P71*0.9235*0.153... how would I make this (Q71) a zero (while leaving p71 as a negative?

Same as before:
Quote:
IF( calculation > 0;
___then the result is calculation;
___otherwise the result is 0)

Now, your calculation is P71*0.9235*0.153, and substituting that into the formula gives:
Quote:
IF( P71*0.9235*0.153 > 0;
___then the result is P71*0.9235*0.153;
___otherwise the result is 0)

Or as a literal Calc formula:
Quote:
=IF( P71*0.9235*0.153 > 0; P71*0.9235*0.153; 0)

This won't affect the value in P71 at all.

Quote:
... I tried ( B70-SUM(C70:L70)) but that didn't work.

Do you mean it gave no answer, or it gave the wrong answer?

Did you put the "=" in front?

I think it's correct algebraically, but then I flub that stuff all the time, so I may be off track.
Back to top
View user's profile Send private message
member
General User
General User


Joined: 25 Dec 2005
Posts: 30

PostPosted: Tue May 01, 2007 10:15 am    Post subject: Reply with quote

Acknac

Everything works the way you have explained it. I appreciate you repeating the part about the calculations in relation to my particular formula. I wanted to hurry up and find out before you had to leave. I am sure this imformation will help me a lot. As to the second part about making the equation shorter, I was missing the equals sign. This is pretty much the best forum experience I have ever had. Again thank you very much.
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Tue May 01, 2007 10:24 am    Post subject: Reply with quote

Quote:
I do visit Format > Cell > Number tab a lot because it I am always having to change my numbers to two decimal places. I can't seem to get it to default to being 2 decimals.
Open a new Calc file, select all cells on sheet(s), apply the number format you want and save the document as your Default Template for Calc. ( Help > Index tab > "default templates".)
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Tue May 01, 2007 11:07 am    Post subject: Reply with quote

member wrote:
I do visit Format > Cell > Number tab a lot because it I am always having to change my numbers to two decimal places. I can't seem to get it to default to being 2 decimals.


Set your default cell format to two decimal points and create a template.

This is one of OOo 's real strengths you can set styles for all your normal formatting needs, create a template and make it your default.
Have a look at the Styles and Templates chapters here. Most of the stuff is written for Writer but all the basics are the same in Calc.
http://documentation.openoffice.org/manuals/oooauthors2/index.html
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
member
General User
General User


Joined: 25 Dec 2005
Posts: 30

PostPosted: Tue May 01, 2007 11:15 am    Post subject: Reply with quote

JohnV

I had a little trouble finding the help part, but I figured out that you were sending me to
the help in tools>options. I found the instructions and set my fonts, borders and numbers as default template and then used the option to set it as default.

I was trying to find it in tools>options>open office calc>view
I had decided it could not be done. You will save me a lot of aggravation with this tweak. It was a common sense thing I should have figured out. Thank you very much.

jrkrideau

I'm off to read your link right now, thank you!!
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Tue May 01, 2007 4:40 pm    Post subject: Reply with quote

How about =MAX(B70-SUM(C70:L70);0)
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Tue May 01, 2007 11:44 pm    Post subject: Reply with quote

TerryE wrote:
How about =MAX(B70-SUM(C70:L70);0)
Very elegant.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group