Yes, Johnny, that is what I originally ask for. There are some other question I expect to be asking in the future but for now I am learning by using various formulas.
Walter On Tue, Jun 2, 2009 at 10:13 AM, Johnny Rosenberg <[email protected]>wrote: > Yes, all of that seems very logical. Was this what you were originally > asking for? > > Johnny Rosenberg > > 2009/6/2 Walter Hildebrandt <[email protected]>: > > Thanks to everyone who helped with this question. > > The following is a summary I made for myself. This might be of interest > to > > some. > > =IF(OR(A1<0;B1<=0);"";(A1-B1)/B1) > > > > Any time there is -100% or a blank in C, *look at the As and the Bs* to > see > > what in the As and Bs. This is because there are various reasons why > > -100%ora blank appear in C. > > > > 1. > > > > If both A and B are positive, and A is greater than B, get positive > > number in C > > 2. > > > > If both A and B are positive, and A is less than B, get a negative > number > > indicating how much less A is than B in C > > 3. > > > > If either A or B is a negative number, *get nothing in C* > > 4. > > > > If both A and B are negative numbers, and A is more negative than B, > *get > > nothing in C* > > 5. > > > > If A has 0, -100% appears in C, except some *Cs stays blank* because of > > either 3. or 4, above > > 6. > > > > If B has 0, C will have -100%, except some *Cs stays blank* because of > > either 3. or 4, above > > 7. > > > > If B has -100%, A will be blank. > > 8. > > > > If A is blank, C will have -100%, except some *Cs stays blank* because > of > > either 3. or 4, above > > 9. > > > > If B is blank, C will have -100%, except some *Cs stays blank* because > of > > either 3. or 4, above. Also some other Cs that had numbers or > > -100%becomes blank. > > > > I originally used just A1, B1, and C1 to explain what I was asking. The > > spreadsheet has a number of cells that are involved with the 3-cell > > question When "C1" is in three of more columns, the changing of either > on > > of the 3 columns gives different results depending on the combination of > > what is in the involved "A1"s and "B1"s cells > > > > Walter > > > > On Mon, Jun 1, 2009 at 8:52 AM, Johnny Rosenberg <[email protected] > >wrote: > > > >> 2009/5/31 JOE Conner <[email protected]>: > >> > Johnny Rosenberg wrote: > >> >> > >> >> Maybe I misunderstood the question, but what if both A1 and B1 are > >> >> negative? If that also should give a blank C1, the formula you got > was > >> >> correct (I guess, I didn't study it closer, but it looks correct). > >> >> However, you can obtain the same thing with the OR function and it > >> >> might look a little bit more straight forward: > >> >> =IF(OR(A1<0;B1<0);"";(A1-B1)/B1) > >> >> > >> >> This means (and this is only for a quick explanation, you can't enter > >> >> it this way, of course): IF A1<0 OR B1<0 THEN "" ELSE (A1-B1)/B1 > >> >> > >> >> Johnny Rosenberg > >> > > >> > <<SNIP>> > >> > I feel constrained to point out that if B=0 your formula will fail. > You > >> > need the less than symbol changed to equals or less than. e.g. B<=0. > >> > > >> > Joe Conner, Poulsbo, WA USA > >> > >> Yes, of course. Didn't think of that, but sure, it's obvious that > >> you're right. I didn't even look at the formula with my brain switched > >> on… > >> > >> =IF(OR(A1<0;B1<=0);"";(A1-B1)/B1) > >> > >> --------------------------------------------------------------------- > >> 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] > >
