I just send an Email stating what is required. In the case of using a misture of 10 and -10 the answers should be either 0 +20 in -20. Neither of the =ABS(A1-B1)/ABS(A1) or the =ABS(A1-B1)/IF(A1=0;1;ABS(A1))
On Mon, Mar 9, 2009 at 4:55 PM, Graham Bowden <[email protected]>wrote: > How about this, I am not absolutely sure I understand what you need to do, > but here goes: > > =ABS(A1-B1)/ABS(A1) > > If A1=0 there will of course be an error - so you might want to decide what > needs to happen in this instance: > > e.g. > > =ABS(A1-B1)/IF(A1=0;1;ABS(A1)) > > Hope this helps > > G > > ----- Original Message ----- From: "Walter Hildebrandt" <[email protected]> > To: <[email protected]> > Sent: Monday, March 09, 2009 10:13 PM > Subject: Re: [users] an empty cell in Calc > > > > 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] >> >> > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > >
