Great job, Brian. The =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))))
worked Walter On Fri, Jun 5, 2009 at 7:59 PM, Walter Hildebrandt <[email protected]> wrote: > > > On Fri, Jun 5, 2009 at 7:32 PM, Brian Barker <[email protected]>wrote: > >> At 16:25 05/06/2009 -0600, Walter Hildebrandt wrote: >> >>> What formula would be used to meet the following conditions? >>> >> >> It is perhaps worth saying that the best way to learn about formulae and >> spreadsheets may not be to lay out your needs for each and every cell in a >> message to a mailing list. There are 2500 readers out there, most of whom >> will have started by reading a manual, searching the help text, or perhaps >> even attending a suitable course. You may want to consider these >> possibilities. >> > > 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] >> >> >
