|
Excel8465
Anon
2014-Oct-15 9:04 pm
[Excel] What is the best formula to use in this situation?Which one formula can I use to take the difference between 2 numbers that would work in the following 4 situations:
1) A1 negative but B2 positive 2) B1 negative but A2 positive 3) A1 negative and B2 negative 4) A1 positive and B2 positive
Thank you in advance! |
|
dave Premium Member join:2000-05-04 not in ohio |
dave
Premium Member
2014-Oct-15 9:12 pm
The 'difference between numbers' is an operation called 'subtraction'. Works the same way regardless of whether the values are positive, zero, or negative.
Thus, A1-B2
But somehow I don't think this obvious answer is what you want.
Maybe you mean you want ABS(A1-B2) ?
Else you'll need to explain more; give some examples. |
|
|
Excel8465
Anon
2014-Oct-15 9:53 pm
For example, if A1=-100 and B1=100. I want the result to be 0. 1) A1-B2 would give me -200 2) ABS (A1 -B2) would give me 200 |
|
Kilroy MVM join:2002-11-21 Saint Paul, MN |
Kilroy
MVM
2014-Oct-15 10:47 pm
ABS(a1)-ABS(b1) should give you the result you are looking for in your example. |
|
Kilroy |
Kilroy
MVM
2014-Oct-15 10:50 pm
You need to do ABS(ABS(a1)-ABS(b1)) so that the number will always be 0 or positive. |
|
dave Premium Member join:2000-05-04 not in ohio
1 recommendation |
to Excel8465
Seems like a very weird computation: and you certainly can't call it the 'difference' between 100 and -100 if the answer is 0. What is it used for? |
|
Msradell Premium Member join:2008-12-25 Louisville, KY
1 recommendation |
Msradell
Premium Member
2014-Oct-16 12:21 pm
said by dave:Seems like a very weird computation: and you certainly can't call it the 'difference' between 100 and -100 if the answer is 0. What is it used for? In a mathematical sense the difference between 100 and -100 is 200, not zero. |
|
Kilroy MVM join:2002-11-21 Saint Paul, MN |
to dave
I'm with you dave , I can't think of a reason why I would want such a calculation. |
|
kfsutops Premium Member join:2002-08-19 Lutz, FL |
to Excel8465
If A1 is -100 and B2 is 100 and you are looking to get to zero wouldn't the formula be =A1+B2 or =sum(A1,b2) |
|
KalfordSeems To Be An Rtfm Problem. MVM join:2001-03-20 Ontario 1 edit |
It's hard to say because the info provided is muddled/incomplete. . . . It may also be that he is looking for the midpoint of two numbers (not difference) - whereby the calculation would be midpoint = (A + B) / 2 |
|
H2OuUp2Happy to be here Premium Member join:2002-03-15 Oklahoma City, OK |
to kfsutops
said by kfsutops:If A1 is -100 and B2 is 100 and you are looking to get to zero wouldn't the formula be =A1+B2 or =sum(A1,b2) I believe this is what you are looking for. It seems you are not asking the difference between two numbers but a balance between the two much like a bank account. If You had $100 in one and -$100 in the other you would have a balance of $0. So using the ADD method you could end up with a positive number number or a negative number depending what the original numbers were in each column. If you are wanting something else, then more information would need to be supplied. |
|