Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Neil R
 
Posts: n/a
Default % of increase or decrease

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Gary''s Student
 
Posts: n/a
Default % of increase or decrease

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''s Student


"Neil R" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
Neil R
 
Posts: n/a
Default % of increase or decrease

Gary

when I used this formula with two positive numbers it gives me a negitive %
50 to 100 gives -100%


"Gary''s Student" wrote:

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''s Student


"Neil R" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Gary''s Student
 
Posts: n/a
Default % of increase or decrease

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.
--
Gary''s Student


"Neil R" wrote:

Gary

when I used this formula with two positive numbers it gives me a negitive %
50 to 100 gives -100%


"Gary''s Student" wrote:

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''s Student


"Neil R" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
glenlee
 
Posts: n/a
Default % of increase or decrease

Assuming your start numbers are in col A and your end numbers are in
col B, you could use this formula:

=IF(A10,(B1-A1)/A1,(B1-A1)/(-1*A1))


On Wed, 23 Nov 2005 16:21:04 -0800, ?B?TmVpbCBS?=
wrote:

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




  #6   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default % of increase or decrease

Gary''s Student wrote:
Always use (start-finish)/start
if start is in A1 and finish is in B1 then the % change in C1


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" wrote:
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


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.

  #7   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default % of increase or decrease

I wrote:
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.


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

works better to cover the case when A1 = B1 = 0.

  #8   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default % of increase or decrease

I wrote:
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.


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

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

  #9   Report Post  
Posted to microsoft.public.excel.misc
glenlee
 
Posts: n/a
Default % of increase or decrease


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(A10,(B1-A1)/A1,(B1-A1)/(-1*A1)))

On 25 Nov 2005 17:06:26 -0800, wrote:

I wrote:
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.


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

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


  #10   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default % of increase or decrease

glenlee wrote:
In the case where the start number in col A is -0- your formulas
below return a result of 100% which is mathematically incorrect.


As I said, the choice of 100% is arbitrary.

Thus I vote for this formula:
=IF(A1=0,"NA",IF(A10,(B1-A1)/A1,(B1-A1)/(-1*A1)))


"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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shortcuts for increase and decrease indents Vaughan Excel Discussion (Misc queries) 3 May 2nd 12 05:34 PM
How do I set a cell that can increase but never decrease? Rich Excel Discussion (Misc queries) 2 November 2nd 05 07:04 PM
Amount of Increase of Wages dah Excel Worksheet Functions 8 September 27th 05 10:31 PM
Increase figures in cells by a percentage (on entire workbook) hornet Excel Worksheet Functions 2 September 5th 05 06:40 AM
How do I increase type size in an Excel drop down list? [email protected] Excel Discussion (Misc queries) 1 January 27th 05 02:27 AM


All times are GMT +1. The time now is 11:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"