% of increase or decrease

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking for a formula that tells me % of increase or decrease, The
problem is that some of my numbers are negitive eg. -147.80 has decreased to
-785.00 I am looking for the % to be a negitive only in this case


Thanks
Neil
 
Always use (start-finish)/start
if start is in A1 and finish is in B1 then the % change in C1:

=(A1-B1)/A1 formatted as percentage
=100*(A1-B1)/A1 formatted as general
 
Gary

when I used this formula with two positive numbers it gives me a negitive %
50 to 100 gives -100%
 
You are correct. For positive numbers the sign must be reversed. This is
because the denominator is now positive.

Think about going from -10 to +10. Here the change is also positive, even
though the denominator is negative.
 
Assuming your start numbers are in col A and your end numbers are in
col B, you could use this formula:

=IF(A1>0,(B1-A1)/A1,(B1-A1)/(-1*A1))
 
Gary''s Student said:
Always use (start-finish)/start
if start is in A1 and finish is in B1 then the % change in C1
Click to expand...

I disagree. When "finish" is greater then "start", most people
expect a positive percentage increase. To that end, "always
use" (finish - start) / start.

For example, if "start" is 100 and "finish" is 110, we expect a
10% increase: (110-100)/100 = 10/100 = 10%. If "finish" is 90,
we expect a 10% decrease, i.e. a -10% "increase":
(90-100)/100 = -10/100 = -10%.

Neil R said:
I am looking for a formula that tells me % of increase or decrease,
The problem is that some of my numbers are negitive eg. -147.80
has decreased to -785.00 I am looking for the % to be a negitive
Click to expand...

I think your expectation is correct. Someone else provided
one formulation that would work, although it can be simplified
(where A1 is "start" and B1 is "finish"):

=IF(A1 > 0, (B1-A1)/A1, -(B1-A1)/A1)

But if you can have mixed positive and negative results, I
would also cover the case where "start" is zero, e.g:

=IF(B1 < A1, -1, 1) * IF(A1 <> 0, ABS((B1-A1)/A1), 100%)

Of course, the choice of 100% is arbitrary.
 
I said:
But if you can have mixed positive and negative results, I
would also cover the case where "start" is zero, e.g:
=IF(B1 < A1, -1, 1) * IF(A1 <> 0, ABS((B1-A1)/A1), 100%)
Of course, the choice of 100% is arbitrary.
Click to expand...

=SIGN(B1-A1) * IF(A1 <> 0, ABS((B1-A1)/A1), 100%)

works better to cover the case when A1 = B1 = 0.
 
I said:
But if you can have mixed positive and negative results, I
would also cover the case where "start" is zero, e.g:
=IF(B1 < A1, -1, 1) * IF(A1 <> 0, ABS((B1-A1)/A1), 100%)
Of course, the choice of 100% is arbitrary.
Click to expand...

The following is better, in case both A1 and B1 are zero:

=SIGN(B1-A1) * IF(A1 <> 0, ABS((B1-A1)/A1), 100%)
 
In the case where the start number in col A is -0- your formulas below
return a result of 100% which is mathematically incorrect. Dividing
by -0- can never achieve a valid result.

Thus I vote for this formula:
=IF(A1=0,"NA",IF(A1>0,(B1-A1)/A1,(B1-A1)/(-1*A1)))
 
glenlee said:
In the case where the start number in col A is -0- your formulas
below return a result of 100% which is mathematically incorrect.
Click to expand...

As I said, the choice of 100% is arbitrary.
Thus I vote for this formula:
=IF(A1=0,"NA",IF(A1>0,(B1-A1)/A1,(B1-A1)/(-1*A1)))
Click to expand...

"To each his own". I prefer homogeneous results -- always
numeric, in this case. It makes it easier to write dependent
formulas. A wise choice for the delta from zero would be a
large percentage for your application, but not so large that
it would skew all other computation. IMHO, 100% usually
fits the bill. But those who struggle with reading, I repeat:
it is arbitrary, it depends on your application, and "to each
his own".

And for Pete's sake, "-A1" is the same as "-1*A1" and
"-(B1-A1)/A1" is equivalent and easier to write.
 
Back
Top