IF FORMULA HELP

[Gail]

I have been trying to create an IF statement that gives a percentage as the
answer.
I need the percentage of change of A compared to B
-I can create the statement if column A is Positive and Column B is Positive
or if A is negative and B is positive but I can not get the statement to
work if A is positive and B is negative.
this is the formula I am using IF(B1<0,+A1/-B1,A1/B1)

I have just not been able to add the next formula to make this work.
Can any one help?

thanks for the help
enjoy your day
gail

 

[Sandy Mann]

Try:

=IF(B1=0,0,ABS(A1/B1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Ron Coderre]

Maybe one of these....?

=IF(N(B1),A1/ABS(B1),"INVALID")
OR
=A1/ABS(B1)


Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[KimCo]

Maybe I missed something, but it seems to me that the formula to figure out
the percentage change from column A to column be would ALWAYS be
B1/A1...wouldn't it?

If A is 8 and B is 10, B/A = 125% (8 x 125% = 10)...
If A is -8 and B is 5, B/A = -63% (-8 x -63% = 5)...
If A is 8 and B is -9, B/A = -113% (8 x -113% = -9)...

Given those three scenarios, what actual result were you looking for?

The only other formula that I could come up with was still not an if
statement, but instead =(B1-A1)/A1 which results in the change BETWEEN the
numbers.

 

[Gail]

I can make my formula work to get the percentages,But I can not get it to
work if A is positive and B is negative with the percentage given as a
percent of positive increase. ie column a 29 column b -1900 my formula
gives a 15% improvement instead of 115%. So what I need is a formula that
will give me
negative to positive with a positive percentage, and a within this formula
negative to negative and positive to negative.


thanks for the help
enjoy your day
gail

 

[Sandy Mann]

Gail,

Are you sure of your logic? I can't get 115% from 29 and -1900 but I can
from 29 and 193.333 with the formula:

=IF(B1=0,0,ABS(A1/B1)+(AND(A1>0,B1<0)))

This is assuming that you are saying from -193.333 to zero is 100% increase
and then 29 is another 15% of 193.333

However, it also means that the nearer that the negative figure in B1 gets
to zero the bigger the percentage increase will be. For example with double
the figure in B1 to -386.333 and the same 29 in A1 you get 107.5% even
although the it is a much bigger change in values and half of the value in
B1 with the same 29 gives 130% change with a smaller change in the figures.
What if one of your values is zero? You would be looking for 0% or
infinity. Just dose not make sense (to me anyway).

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk