calculate the signed difference

[flyngcoln]

Hi,
I'm trying to calculate the signed difference between two numbers.

I'm wanting to show if the range between two numbers is positive and negative.

Evertime I calculate the difference between two numbers it always shows up as positive, even if the second number is less than the first.

For e.g.

55%
18%
diff: 37%, but I want it to show that it is -37%

Is there a formula that will adapt depending on whether the second number is bigger or smaller than the first?
Thanks.

 

[flyngcoln]

I'll just add for context:
It's increase/decrease in growth over a time period:

2015: 55%

2017: 18%

 

[dreid1011]

Maybe someone has a better solution, but this is what I came up with.

=(A1-A2)*100&"%"&IF(A1>A2," decrease"," increase")

 

[flyngcoln]

Just worked it out for anyone interested:

=(new value - old value)/2 (/2 is for average change)

 

[flyngcoln]

Thank you dreid1011 - I'll see if yours works for me too, thanks!

 

[CJ_22]

Doesn't =(A1-B1)*-1 accomplish this?

 

[dreid1011]

Thank you dreid1011 - I'll see if yours works for me too, thanks!

You're welcome. I may have misunderstood though. I thought you wanted it to display the text "increase" or "decrease". If not, CJ_22's suggestion looks like it does what you want. Just format the result cell as percent.

And are you sure /2 gives you the desired result? With 55% (old) and 18% (new): (18-55)/2 returns -.185. Using /1 would return -.37 as I expect that is what you want? If not, please disregard.

 

[meytithveasna]

Simply
= B1-A1
Custom format,Type box:enter the following: 0.00%;[Red]-0.00%
Click OK.