Walter, I'd suggest that you attempt to implement this yourself, but to it sub-expression by sub-expression, one at a time. For instance, start with your original equation: (which probably isn't surviving email line splitting!)
IF(OR(A1="";B1="");IF(OR(A1<0;B1<0);"Neg,Emt";"Empty");IF(OR(B1<0;A1<0);"Negative";IF(B1>0;(A1-B1)/B1;IF(A1=0;1;"∞%")))) To learn how this works you can do this one piece at a time putting each piece in it's own cell and then building up the sort of operation you are looking for: D1 => OR(A1="",B1="") E1 => OR(A1<0,B1<0) the you might do something like F1 => IF(E1, "Neg,Emt", "Empty") Now, I've probably messed up the little bit of breakdown I've done here because the copy that came to me was split funny, but the idea should be clear. Each part of the equation is done step by step. the result is saved. You use the result in another part of the equation. This is my preference for developing my ideas. I note that this equation does both: OR(A1<0,B1<0) and OR(B1<0,A1<0) It's the same equation with the inputs turned around. Do you need them both? Answer: No, not if the value is in a cell and you can grab it. Now, on simple math like this it doesn't matter much, but once you start calculating really big things you don't want to do it multiple times. Good luck with whatever it is you are working on and with your learning. Cheers, Mark 2009/6/9 Walter Hildebrandt <[email protected]>: > The 7. condition is corrected below. Disregard the 7. condition previously > sent. > > 2009/6/8 Walter Hildebrandt <[email protected]> > >> The formula IF(OR(A1="";B1="");IF(OR(A1<0;B1<0);"Neg, >> Emt";"Empty");IF(OR(B1<0;A1<0);"Negative";IF(B1>0;(A1-B1)/B1;IF(A1=0;1;"∞%")))) >> works well for the following 5 conditions below. Please add conditions 6 >> and 7 to the formula. I hope to compare the original formula to the new >> formula and thereby learn something about the structure of formulas when IF >> and OR are in one formula. >> >> 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 "Negative" appears in >> C1. >> 4. >> >> If either A1 or B1 is empty, the word "Empty" appears in C1. >> 5. >> >> However, if both the conditions in 3) and 4), above, are in effect, the >> word "Neg, Emt" appears in C1. (if A1 had a negative number and B1 is >> empty, >> the word "Neg, Emt" would appear in C1 or if A1 is empty and Bi had a >> negative number, the word "Neg, Emt" would appear in C1 ) >> 6. >> >> If 0 (zero) appeared in both A1 and B1, the word “00” appears in C1. >> 7. >> >> If 0 (zero) appeared in either A1 or B1, and the 2nd A1 or B1 had >> either a positive number, or a negative number, the word “0" appears in C1 >> (Stated a different way: If 0 (zero) is in either A1 or B1 while either a >> positive or a negative number is in the other A1 or B1 cell) >> >> > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
