How to set up a formula that will give me both positive and neg. .

[Guest]

want to know how to setup a formula in excel that can be copied to all cells
and spit out negative and positive percentages depending on the data. For
example
5/10= 50%......I can get that, but I want a common formula that can produce
that AND 10/5= - 50%.

 

[Guest]

Craig,
Give us an idea of what cells you areworking with ie A1/B2 and the formula
will be pretty simple.

 

[Bob Phillips]

10/5 is 200%. What rule determines that is -50%?

 

[Guest]

Hi,
If i understand correctly:

Say A2 and B2 are the cells
1- If you just want the negative formula:
= - A2/B2
2- If you want negative or positive formula based on a condition:
= IF( condition_for_positive, 1 , -1 ) * A2/B2
eg: say you want the percentage to be positive if A2 is greater than
1000, else negative.
= IF( A2 > 1000, 1 , -1) * A2 / B2
Another way is to use a cell, Say $A$1, to set the negative or positive
view:
The following shows positive if $A$1 is "p", else it shows negative:
=IF ($A$1="p" , 1 , -1) * A2/B2

3- Finally, and a bit different, if you want to show all NUMBERS as positive
or negative (all of them), based on the previous example:
= IF ($A$1="p" , 1 , -1) * abs(A2/B2)

I hope this helps,
Sebastien

 

[Guest]

Assuming data is in cells A2 and B2, the formula should be...
=IF(B2>=A2,A2/B2,-B2/A2)
Hope this helps,
Gary Brown