Percent change with negative numbers

[artz]

Could anyone in the Forum show how to caluculate percent change with both positive and negative numbers? The simple ((B2-A2)/A2)*100 does not compute properly with negative numbers.

Suggestions?

Thanks,

Art

 

[Rick Rothstein]

I guess this should work for you also...

=100*ABS(B2-A2)/ABS(A2)

 

[Mr HR]

Re: Percent change with negative numbers and positve numbers

Hi i am trying to work out a percentage. sga expenses which is a positive number and i am trying to divide it by net earnings which is a negative number how do i do this

 

[Number4]

Re: Percent change with negative numbers and positve numbers

Running through this one myself today, comparing year/year profits. Excited at first about the =(F15/I15-1)*SIGN(I15) formula, until I realized a larger negative number generated a smaller increase.

This website puts it best.

Percent increase from a negative number to a positive number - Math Central

"The increase of $176,387.00 from -$110,333.00 to $66,054.00 is a meaningful number but to attempt to express it as a percentage increase is meaningless. Percentage increase should only be used when the beginning and ending numbers are both positive."

From an operating stand point, it's significant that you had a dollar increase that put you in the black. It's what pays the bills. But as a percentage, it has no value.

My new formula is simply =IF(OR(I15=0,I15<0),0,(F15/I15-1))

Measuring the success via a percentage of increase is only meaningful when in both years you had positive results. You did this good last year and this good this year, how much better vs. last year was this year. If last year was in the red, you aren't measuring success year/year, your are thanking the Lord that your head is finally above water.

Quite simply, if last year was a negative dollar value, the percentage of increase is meaningless. Think about it, it has NO value.

 

[taher9990]

Re: Percent change with negative numbers and positve numbers

Use this in Excel where A1 is the new value and B1 is the old value:

=IFERROR(IF(AND(A2<0,B2<0,A2>B2),((A2-B2)/B2)*-1,IF(AND(A2<0,B2<0,A2>B2),

((A2-B2)/B2),IF(AND(A2<0,B2<0),((A2-B2)/B2)*-1,IF(AND(A2>0,B2<0),((A2-B2)/B2)*-1,IF(B2<a2,((a2-b2)

/B2),IF(B2>A2,((A2-B2)/B2),ABS(((A2-B2)/B2))))))))*100,"")

Explanation:
The above formula is basically this formula ((A2-B2)/B2)*100
But it only calculates positive numbers
But if you want to know the change percentage for all numbers my given formula will solve the issue and it handles all below situations:

1. A>0, B>0, A
2. A>0, B>0, A>B
3. A<0, B>0, |A|
4. A<0, B>0, |A|>B
5. A>0, B<0, A<|B|
6. A>0, B<0, A>|B|
7. A<0, B<0, |A|<|B|
8. A<0, B<0, |A|>|B|
</a2,((a2-b2)>