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] > >
