On Jan 11, 4:25*pm, suesolotel
wrote:
i need to have a formula that will calculate % change for loss to profit
and profit to loss and profit to profit

The problem is: there is no agreement (that I know of) on how to
report the difference between negative and positive values.

The following will probably fit your needs (format as Percentage),
where you want to report the percentage change from A1 to A2:

=A2/A1 - 1

Thus:

1. If profits rise from 100 to 200, the formula results in 100%; i.e.,
"a 100% increase in profits".

2. If profits decline from 100 to 0, the formula results in -100%;
i.e., "a 100% decrease in profits".

3. If profits of 100 decline to a loss of -100, the formula results in
-200%; i.e, "a 200% decrease in profits".

4. If losses go from -100 to -200, the formula results in 100%; i.e.,
"a 100% increase in losses". (I believe that fits the vernacular,
even though it is a numerical decrease.)

5. If losses go from -100 to 0, the formula results in -100%; i.e. "a
100% decrease in losses". (Again, I believe that fits the vernacular,
even though it is a numerical increase.)

Note: If any of those cases do not fit your use of terms, please
enumerate the cases and how you would express them.

Problems arise with two special cases:

6. Profits of 0 increase or turn into losses. If profits go from 0 to
100, what would you say the percentage increase is? Before you
answer, consider the case of profits rising from 0.01 to 100.

7. Losses of -100 turn into a profit of 100. The formula would
reflect "a 200% decrease in losses". Technically consistent with #5
(and perhaps #3) above. But am I not sure that is what you would want
to say, since it still sounds like a loss.

To account for the special cases, you might use the following formula:

=if(A1<0, if(and(A1<0,A20), "dont know", A2/A1 - 1), if(A2=0, 0,
if(A2<0, "dont know", "dont know")))

You fill in the "dont know" parts, probably replace them with numeric
expressions, not strings. I separated those parts in case you want to
choose different expressions. (I suspect you will.) Otherwise, the
formula can be simplified.

HTH.