Mark The formula originally came from Johnny Rosenberg. He also said he setup a spreadsheet to test all the different possible. I have made a similar spreadsheet with all the different possibilities in rows A and B. I will Email you the spreadsheet (I believe attachments can not be sent to the Users group. i have some other stuff on the spreadsheet such as color coding the different parts of the formula.
Walter . On Tue, Jun 9, 2009 at 10:25 AM, Mark Knecht <[email protected]> wrote: > 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] > >
