The =ABS(B1) + ABS(C1) has to work in all of the four conditions. it only worked when B1 was 10% and C1 was -10%. D1 had 20% (a positive number) regardless of whether there was positive or negative numbers in B1 and C1.
On Mon, Mar 9, 2009 at 3:40 PM, JOE Conner <[email protected]> wrote: > Walter Hildebrandt wrote: > >> Great, the =IF(ISNUMBER(A1);A1-3;"") >> >> The following is a summary of what worked; >> >> number one =IF(A1=0;��;A1) if A1 is empty get empty. If A1 has a 0 get an >> empty cell. If there is a number that is not 0 get that number. >> >> Number two =IF(ISBLANK(A1);��;A1) and =IF(ISNUMBER(A1);A1;��) produce the >> same following results >> >> if A1 is empty get empty. If A1 has a 0 get a 0. If there is a number that >> is not 0 get that number. >> >> Number three =IF(ISNUMBER(A1);A1-3;��) does the following; >> >> If A1 is empty then B1 is empty. If A1 has any number in it, that number >> in >> A1 is reduced by 3. If there is 0 in A1 then B1 would be -3. If there is >> a >> 4 in A1 then B1 would be 1. If there is a 3 in A1 then B1 would be 0. >> >> >> That is the good new. The bad news is that I have another question. >> >> In both A1 and B1 there is a percent that can be either a positive or a >> negative percent. (for example it could be -10% or 10%) >> In C1 there should be the percentage by which A1 is greater or less than >> B1. >> (for example if both A1 and B1 is 10%, the difference is 0%. If A1 is 10% >> and B1 is -10%, the difrerence is 200%) >> >> When either =(A1-B1)/A1 or =(-A1_B1)/A1 is in C1 there is a problem. In >> three out of four combinations either formula works. It works when a >> positive number is in both A1 and B1. It works when a negative number is >> in both A1 and B1 The formula does not work in one of the four >> cominations >> when there is a negative number in A1 and a postive number in B1, The >> formula does not work in one of the four cominations when there is a >> positive number in A1 and a negative number in B1. >> >> Is there an IF to correct this or is there some other way to get the % or >> the ratiio that measurs how much A1 is greater than or less than B1 >> >> >> > Walter, there is a possible solution using the ABS function. For example, > if B1 has -10% and C1 has +10% you could > say in D1 enter =ABS(B1) + ABS(C1) and this should get you +20%. > I hope this helps. > > Joe Conner, Poulsbo, WA USA > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] >
