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]
