Johnny, I tested all the different possibilities and found, assuming I did not make a mistake, there was one condition that did not work properly. When A1 was empty and there was a negative number in B1, C1 had Empty. C1 should be Empty,Negative.
The following is some information I have gathered and recorded in writing. The first formula is from you. =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;"∞%")))) 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. 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) The following is from Brian Barker =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)))) Find IF on OOo websiteI did the following to get information from the OOo website; Go to OOo website > I need help with my OpenOffice.org > If you are a new user to OpenOffice.org, we recommend that you read the latest User Guide available for your version: located . *here* (click on the *here* > OOo 3 User Guides (Wiki) > Spreadsheets with OOo Calc 3 > Chapter 7 Using Functions and Creating Formulas > In right-hand column, click Using Functions > click Functions listed by Category (under Logical functions, click IF) Logical Functions The Logical functions allow boolean logic decisions. *・ *AND<http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_AND_function>, FALSE<http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_FALSE_function>, IF<http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_IF_function>, NOT<http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_NOT_function>, OR<http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_OR_function>, TRUE<http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_TRUE_function> IF Returns one of two values, depending on a test condition. Syntax: *IF(test; value1; value2)* where: *test* is or refers to a logical value or expression that returns a logical value (*TRUE* or *FALSE*). *value1* is the value that is returned by the function if *test* yields *TRUE.* *value2* is the value that is returned by the function if *test* yields *FALSE*. If *value2* is omitted it is assumed to be *FALSE*; if *value1* is also omitted it is assumed to be *TRUE*. Example: *IF(A1>5; 100; "too small")* returns the number 100 if A1 is greater than 5, and the text "too small" otherwise. *IF(1>2; "nonsense")* returns *FALSE* - because *value2* has been omitted and 1 is not greater than 2. *IF(2>1)* returns *TRUE* - because both *value1* and *value2* have been omitted and 2 is more than 1. *IF(1=2; 1/0; SQRT(4))* returns *2*, the square root of *4*. *IF()* only calculates the value chosen - in this case *1/0* would give a #DIV/0! error, but is not calculated. *See Also* - COUNTIF<http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_COUNTIF_function> - SUMIF<http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_SUMIF_function> On Sat, Jun 6, 2009 at 9:16 AM, Johnny Rosenberg <[email protected]>wrote: > 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 > >
