[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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 =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!
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( 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.
JohnV

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.
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.
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".
JohnV

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.
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 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)
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 =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.
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 ( 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.
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.
JohnV

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".)
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732

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
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
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!!
TerryE
Super User

Joined: 16 Jul 2006
Posts: 550
Location: UK

 Posted: Tue May 01, 2007 4:40 pm    Post subject: 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.
huwg
Super User

Joined: 14 Feb 2007
Posts: 890

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

Very elegant.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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