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]
