2009/6/3 Walter Hildebrandt <[email protected]>: > 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
Well, good luck then and welcome back with more questions. Johnny Rosenberg > > 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] >> >> > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
