|
Answer» 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!
Would that not simply be B-A?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"You definitely want to leave out the ABS() part of the forumla out since an absolute number is ALWAYS positive.
Quote from: Mark. on November 03, 2017, 09:48:38 PM 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 before the '+' and before the '-' and change '<' to '['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.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.
|
