View previous topic :: View next topic 
Author 
Message 
member General User
Joined: 25 Dec 2005 Posts: 30

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


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 =B70C70D70E70F70G70H70I70J70K70L70.
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 


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

Posted: Tue May 01, 2007 6:53 am Post subject: 


Quote:  =IF( B70C70D70E70F70G70H70I70J70K70L70 > 0; B70C70D70E70F70G70H70I70J70K70L70; 0) 
Which you can simplify a bit to:
Quote:  =IF( B70SUM(C70:L70) > 0; B70SUM(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 


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

Posted: Tue May 01, 2007 6:55 am Post subject: 


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 


member General User
Joined: 25 Dec 2005 Posts: 30

Posted: Tue May 01, 2007 7:10 am Post subject: 


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 


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

Posted: Tue May 01, 2007 7:15 am Post subject: 


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 


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

Posted: Tue May 01, 2007 7:21 am Post subject: 


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 


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

Posted: Tue May 01, 2007 7:30 am Post subject: 


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 twoway branch:
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 


member General User
Joined: 25 Dec 2005 Posts: 30

Posted: Tue May 01, 2007 8:45 am Post subject: 


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 =B70C70D70E70F70G70H70I70J70K70L70, and wanted the negative answer how would I do that? I tried ( B70SUM(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 


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

Posted: Tue May 01, 2007 9:01 am Post subject: 


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


member General User
Joined: 25 Dec 2005 Posts: 30

Posted: Tue May 01, 2007 10:15 am Post subject: 


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 


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

Posted: Tue May 01, 2007 10:24 am Post subject: 


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 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

Posted: Tue May 01, 2007 11:07 am Post subject: 


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 


member General User
Joined: 25 Dec 2005 Posts: 30

Posted: Tue May 01, 2007 11:15 am Post subject: 


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 


TerryE Super User
Joined: 16 Jul 2006 Posts: 550 Location: UK

Posted: Tue May 01, 2007 4:40 pm Post subject: 


How about =MAX(B70SUM(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 


huwg Super User
Joined: 14 Feb 2007 Posts: 890

Posted: Tue May 01, 2007 11:44 pm Post subject: 


TerryE wrote:  How about =MAX(B70SUM(C70:L70);0)  Very elegant. 

Back to top 


