Well, anyway, here's my contribution… it seemed to work when I tested it:
=IF(OR(A1="";B1="");IF(OR(A1<0;B1<0);"Empty,
Negative";"Empty");IF(OR(B1<0;A1<0);"Negative";IF(B1>0;(A1-B1)/B1;IF(A1=0;1;"∞%"))))
On the other hand I a bit too late, you already got a solution. Maybe
I used another method to do this, I don't know. I just made a simple
table with all possible combinations of A1 and B1 and what resyult I
wanted. In some cases more than one combination gives the same result
((A1-B1)/B1 is one of the results), so sorting by result makes it easy
to see what combinations to check first. For example most combinations
with one or more empty cells gives the same result ("Empty"), so I
check for that first. If it's true, I also check for negative values,
which gives the result ”Empty, Negative” and so on.
That way the last IF statements doesn't have to be very specific,
since most combinations are already filtered out by the other IF
statements.
I enter every IF statement one by one and check the ”formula so far”,
letting the result be ”Whatever” if other combinations than those I
did so far. For every new IF statement I replace the ”Whatever” by the
new IF statement. Also, for every new IF statement I added ”OK” in a
fourth column of my table for those combinations that are OK so far.
Makes it easy to see what's done and what to do next.
Maybe there are better methods but this one worked for me today and I
got almost no errors at all on my way from a simple uncomplete formula
until the final formula was done. Since i tested every step as the
formula grew, I didn't have to test everything when the formula was
finished and the need for debugging was minimal.
Kind regards
Johnny Rosenberg
2009/6/6 Johnny Rosenberg <[email protected]>:
> About formulas in OpenOffice.org, they are the same as those in Excel,
> which probably makes it somewhat easier to find information. Some
> people say that parameters are separated with commas in Excel and semi
> colons in OpenOffice.org Calc, but I've seen Excel examples where they
> use semi colons as separators, so I'm a bit confused there…
>
> Johnny Rosenberg
>
> 2009/6/6 Walter Hildebrandt <[email protected]>:
>> Thank you Joe for the list of places that have information. Quite a few
>> years ago I started the website tutorialsforopenoffice.org. You included a
>> lot of links to my website. I started the website because of the difficulty
>> of finding into about OpenOffice. I gave the website to someone for free.
>> He was going to develop the website more but that has not happened. (By
>> give the website free, I mean that I let the person register the website in
>> his name instad of my name)
>>
>> My former website does not have anything about formuras such as =IF. I
>> suspect the links that were not for my former website will not give the =IF
>> information.
>>
>> I find it very difficult to use the official OpenOffice website. I used it
>> an hour or so ago and did not find any =IF informaiton. (I am uisng the
>> phrse "=IF" because i do not know what to call that feature and the other
>> simular features).
>>
>> My former website is far from being complete. I did learn that one of the
>> best ways to teach OpenOffice was in a one, two, three, etc step
>> tutoorials. The problem is that my former website needs a lot more
>> tutorials. (The "How To"s are very similar to the tutorials idea)
>>
>> Walter
>>
>> On Fri, Jun 5, 2009 at 9:30 PM, JOE Conner <[email protected]> wrote:
>>
>>> Walter Hildebrandt wrote:
>>>
>>>> On Fri, Jun 5, 2009 at 7:32 PM, Brian Barker <[email protected]
>>>> >wrote:
>>>>
>>>>
>>>
>>> <<SNIP>>
>>>>
>>>> Brian, you point is well taken. I have tried to find a manual to learn
>>>> about the various formula that can be used in OOo buy can not find such a
>>>> thing. Do you know where such information is available?
>>>>
>>>> Walter
>>>>
>>>>
>>>>
>>>>> 1) If both A1 and B1 are positive numbers, and A1 is greater than B1,
>>>>> get
>>>>>
>>>>>
>>>>>> a positive number, in C1. The number in C1 is the percentage by how much
>>>>>> A1
>>>>>> is greater that B1.
>>>>>>
>>>>>> 2) If both A1 and B1 are positive, and A1 is less than B1, get a
>>>>>> negative
>>>>>> number, in C1. The number in C1 is the percentage by how much A1 is less
>>>>>> than B1.
>>>>>>
>>>>>>
>>>>>>
>>>>> In this case, the "percentage by how much A1 is less than B1" is a
>>>>> *positive* number, in fact. The percentage by which A1 is greater than
>>>>> B1
>>>>> would be negative. And this is an example where a little learning about
>>>>> formulae (or arithmetic itself?) might help you to see that these two
>>>>> conditions are in fact one.
>>>>>
>>>>> 3) If either A1 or B1 is a negative number, the word "Neg" appears in
>>>>> C1.
>>>>>
>>>>>
>>>>>> 4) If either A1 or B1 is empty, the word "Em" appears in C1.
>>>>>>
>>>>>> 5) If both the conditions in 3) and 4), above, are in effect, the word
>>>>>> "Neg Em" appears in C1. *(for example, if A1* had a negative number and
>>>>>> B1
>>>>>> is empty, the word "Neg Em" would appear in C1)
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> =IF(AND(OR(A1="";B1="");OR(A1<0;B1<0));"Negative/Empty";IF(OR(A1="";B1="");"Empty";IF(OR(A1<0;B1<0);"Negative";IF(B1=0;"B
>>>>> is zero!";(A1-B1)/B1))))
>>>>>
>>>>> You'll need to format column C as Percent.
>>>>>
>>>>> At 18:59 05/06/2009 -0600, Walter Hildebrandt wrote:
>>>>>
>>>>>
>>>>>
>>>>>> OK, if A1 is positive and B1=0 it is C1 can have a "oo" with or without
>>>>>> a
>>>>>> "%"
>>>>>>
>>>>>>
>>>>>>
>>>>> But you only get an "oo" with Typhoo (see
>>>>> http://en.wikipedia.org/wiki/Typhoo )!
>>>>>
>>>>> I trust this helps.
>>>>>
>>>>> Brian Barker
>>>>>
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [email protected]
>>>>> For additional commands, e-mail: [email protected]
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>> Here are a few to get you started:
>>>
>>>
>>> http://documentation.openoffice.org/manuals/oooauthors2/0314CG-DescriptionOfFunctions.pdf
>>>
>>> http://wiki.services.openoffice.org/wiki/Documentation/Reference/Calc_functions
>>> http://www.learnopenoffice.org/calccontents.htm
>>> http://www.linuxjournal.com/node/1000204
>>> http://www.linuxjournal.com/node/1000211
>>> http://documentation.openoffice.org/HOW_TO/spreadsheet/calc1_EN.html
>>> http://www.openofficetips.com/2005/02/
>>> http://www.tutorialsforopenoffice.org/category_index/spreadsheet.html
>>> http://www.tutorialsforopenoffice.org/tutorial/Spreadsheet_Math.html
>>> http://spreadsheets.about.com/od/otherspreadsheets/ss/080616_24_calc.htm
>>>
>>> Joe Conner, Poulsbo, WA USA
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [email protected]
>>> For additional commands, e-mail: [email protected]
>>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
