Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
% 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
% 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
% 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
% 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
% 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
% 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
% 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
% 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
% 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
% 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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortcuts for increase and decrease indents | Excel Discussion (Misc queries) | |||
How do I set a cell that can increase but never decrease? | Excel Discussion (Misc queries) | |||
Amount of Increase of Wages | Excel Worksheet Functions | |||
Increase figures in cells by a percentage (on entire workbook) | Excel Worksheet Functions | |||
How do I increase type size in an Excel drop down list? | Excel Discussion (Misc queries) |