Excel support: Calculating difference between columns

[Sophie]

Hello,

I have two columns of data and I would like to be able to calculate the difference between the two. So I want Column C to show me whether the data in Column A is higher or lower than the data in Column B and by how much.

As it stands, I have applied this formula to Column C: =ABS(A2-B2)

However, this doesn't tell me which cell has the higher number, just the difference between the two. I want it so that if A is 5 and B is 4, then C shows as -1. If, however, A is 5 and B is 6, I want C to show as 1 (or +1 but Excel doesn’t like that!).
 
Is there a way of doing this?

Any help would be much appreciated. As you can probably tell from the way I have worded this, I am not naturally mathematical! And this is part of the reason I can’t find the answer: I don’t know the terms for what I want to do.

Thanks in advance!

[BC_Programmer]

Would that not simply be B-A?

[Mark.]

BC is right, just have =B1-A1 in C1


I would go one step further and right click C1, Format Cells, Number tab, Custom and type this in the Type: field +#,##0;-#,##0;"same"


the breakdown of that 'formula' is negative amount;positive amount;zero amount
you'll notice you can get your + symbol this way too.
change "same" to "zero" or 0 depending on your liking.


you can also go crazy and have something like +#,##0;-#,##0;"same"

[strollin]

You definitely want to leave out the ABS() part of the forumla out since an absolute number is ALWAYS positive.

[Mark.]

you can also go crazy and have something like +#,##0;-#,##0;"same"

the above example didn't display correctly, the '[' and ']' are forum code for special formatting.

there should be <Blue> before the '+' and <Red> before the '-' and change '<' to '['

[Houb]

You can use an IF statement and I would be happy to write one up for you if the previous solutions don't cover what you need.

[soybean]

You might also find Conditional Formatting helpful.  You could use it to display numbers in column C in one color if the difference between column A and B is positive and another color if the difference is negative.