Difference with negative and positive numbers 2

[MaryMaryMorrison]

I need to get the difference between last months estimated profit and this months estimated profit.
The problem is if I have -31 for last month and -51 for this month I need the difference to be a positive number 20 not -20.
The other problem is if I have 56 for last month and -20 for this month I need the difference to be -76 which is actually the change in monthly estimated profit not 36.

I've tried 2 if statements but I can't seem to get the correct results.

this mth profit Last profit difference
$(52,018.26) $(31,101.00) $(20,917.26)- need it to be 20917.26
$(19,881.01) $56,040.76 $ $36,159.75 - need it to be $(75,921.77)

I appreciate anyone's help.

Mary

 

[Andrzejek]

And that's how we arrived back to original Skip's response: "Try the ABS() function."

Have fun.

---- Andy

 

[SkipVought]

I usually like the shortest distance between two points!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Deniall]

I don't think the OP wants the ABS function, because in her 15May13@12:29 post she gave two scenarios where she wants the answer to be negative.

As others have said above, "difference" is defined in the rules of arithmetic, and "difference" is in effect another word for subtraction. Excel follows the rules of arithmetic (well, mostly it does). So, if it is genuinely the difference that the OP wants then a simple "=K15-L15" should work in all cases. The sign of the result just pops out right.

However her scenarios 1 and 3 in that same post are either incomplete (in that she needs sub-scenarios for the K>L and K<L cases), or her definition of "difference" differs (sic) from the generally accepted one.

Does the problem come from the formatting applied to the cells, rather than the values the cells contain?

 

[mintjulep]

I don't see the business case for a calculation that returns the wrong answer. However it does perhaps explain why Mary's company loses money three out of four months.

 

[SkipVought]

Way to go mint! Why didn't I think of that! ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MaryMaryMorrison]

LOL! Yes this is for a particular client and he looks at things the wrong way. I am just trying to pacify him. I felt odd posting my question b/c technically it doesn't make sense to look at the difference that way.

Anyways I do appreciate the help!

Mary

 

[zelgar]

Please give a numeric example of all your cases and your expected results. Also, did you try my formula I posed on 5/15 :

=+(K15*J15/(ABS(J15*K15))*ABS(K15-J15))

 

[MaryMaryMorrison]

This is the formula that worked from Randy

=IF(AND(K15<0,L15>0),ABS(K15-L15)*-1,ABS(K15-L15))

The only piece I'm missing is if K15 is less than L15, I need it to return a negative number

57-63= -6

 

[mintjulep]

=SIGN(K15)*SIGN(L15)*ABS(K15-L15)

It will get you what you ask for in every case, even though it makes no sense.

 

[SkipVought]

@mint, TWO great tips! Care to go for a Hat Trick? ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

For that I'd have to guess what the desired result would be in the event that either number is zero. Currently my formula will return zero in that unspecified condition.

 

[zelgar]

I think there were more cases than the 4 you listed on May 15th, depending on if K>L or K<L, since if K&L>0, but K<L you expect a negative answer. For example, please give the results of the following numbers and we'll try to get you the formula that will fit:

K	L
8	2
2	8
2	-8
-2	8
-2	-8
-8	-2
-15	-10
-10	-15

 

[MaryMaryMorrison]

So guess what I've convinced my client to look at the variances the NORMAL Way! Thank Goodness. But thanks for all your effort!

Mary

 

[SkipVought]

Using zelgar's example and mint's formula, the logic can be stated as

When both subtrahend and minuend have the same sign then the difference sign is positive.
When neither subtrahend nor minuend have the same sign then the difference sign is negative.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

No problem. I think we all were getting a little confused by what you were wanting. In the end, I think your customer will be better off using the normal method instead of their weird way of tyring to calculate the variances.