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

 

[SkipVought]

hi,

Try the ABS() function.

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

 

[MaryMaryMorrison]

Thanks Skip,

I'm not sure how to add the abs to this formula, this is the formula I used for the first equation and it returns the positive 20K, unfortunately when I use it on the second equation it returns a positive value also instead of the negative 75k

=IF(OR(K12>L12,+K12-L12,L12-K12),IF(K12>L12,+K12-L12,L12-K12),IF(OR(K12<L12,+K12-L12,L12-K12),IF(K12>L12,+K12-L12,K12-L12)))

I used this one =IF(OR(K15>L15,+K15-L15,L15-K15),IF(K15<L15,+K15-L15,L15-K15)) on the second equation and it works, but if i use it on the first equation it returns a negative for him to.

I need one that does both?

 

[Andrzejek]

Could you explain how can you take:

$(52,018.26) $(31,101.00) $(20,917.26)- and come up with 20917.26
$(19,881.01) $56,040.76 $ $36,159.75 - and come up with $(75,921.77)

in plain English.

I assume $(52,018.26) is -52,018.26


Have fun.

---- Andy

 

[MaryMaryMorrison]

Hi Andy,

this mth profit last month profit diff in profit
$(52,018.26) $(31,101.00) $(20,917.26)

- last month we were expecting a loss of only -31101.00 but this month we anticipated -52018.26 in loss so that means the difference is 20917.26 additional in loss.

$(19,881.01) $56,040.76 $ $36,159.75 - and come up with $(75,921.77)

- we were expecting 56040.76 in profit last month but this month we are expecting a loss of -19881.01 plus the loss of the 56040.76. = -56040.76 plus -19881.01 = -75921.77 change.

I hope that make sense???

 

[SkipVought]

???
[tt]
OR(K15>L15,+K15-L15,L15-K15)
[/tt]
okay follow me on this...

K15>L15 returns TRUE or FALSE
K15-L15 returns a numeric value I presume as does L15-K15.

What is all that anyhow?

[tt]
=ABS(K15-L15)
[/tt]




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

 

[MaryMaryMorrison]

yes ? im confused, sorry

 

[SkipVought]

So, will that work for you?

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

 

[MaryMaryMorrison]

No it doesn't work,

I'm trying to say if K15 <0 and L15<0 then abs(k15)+(l15) and if k15<0 and L15 >0 then k15+(l15*-1)
and if k15>0 and L15 >0 then k15+l15 and if K15>0 and l15<0 then k15+abs(l15).
so there are 4 scenarios:
1. if k is negative and l is negative then return the diff as a positive number
2. if k is negative and l is positive then return a negative number
3. if k and l are positive return the diff as a positive number
4. if k is positive and l is negative return diff as a positive number.

??help - sorry to be such a pain. Mary

 

[SkipVought]

Just wrap the whole thing in ABS().

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

 

[randy700]

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

Randy

 

[zelgar]

Try this formula:
=+(K15*J15/(ABS(J15*K15))*ABS(K15-J15))

 

[SkipVought]

I need the difference to be a [red]positive[/red] number

$(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 [red]$(75,921.77)[/red]

I don't get your intended implied logic! [red]$(75,921.77)[/red] is [red]NEGATIVE[/red]!!!

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

 

[Andrzejek]

That's why I asked:

$(52,018.26) [red]???[/red] $(31,101.00) [red]???[/red] $(20,917.26) = 20917.26
$(19,881.01) [red]???[/red] $56,040.76 [red]???[/red] $36,159.75 = $(75,921.77)

What should be in [red]???[/red] : + - / x (-1) ?

- we were expecting [blue]56040.76 in profit[/blue] last month but this month we are expecting a loss of -19881.01 plus [blue]the loss of the 56040.76 [/blue]. = -56040.76 plus -19881.01 = -75921.77 change.

Which is it? A profit or the loss of 56040.76 ...?

Cannot wrap my head around the math here. :-(

Have fun.

---- Andy

 

[mintjulep]

I need to get the difference between last months estimated profit and this months estimated profit

This Month - List Month = Difference

Example 1: This Month = -51 Last Month = -31

-51 - -31 = -20

Example 2: This Month = -20 Last Month = 56

-20 - 56 = -76

Math is math and it follow rules. Those rules dictate the results above.

You can't arbitrarily change the rules to make the answer whatever you want.

In both examples above the performance was WORSE for This Month than for Last Month. Worse is reflected by a negative number.

If we make up an example where this month is BETTER than last month you will get a positive number.

Example 3: This Month = -10 Last Month = -25

-10 - -25 = 15

 

[SkipVought]

YES!
[tt]
furthermore...
A..............B
$(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)
[/tt]
[tt]
$(20,917.26) = B - A

but...

$36,159.75 = B + A
[/tt]
Where is the consistency?

Your question simply does not make any sense!

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

 

[MaryMaryMorrison]

Randy,

Your formula worked! Except now I need to add if l15 is less than k15 then k15-l15?

Mary

 

[MaryMaryMorrison]

Randy actually i meant if l15 < k15 then l15-k15

 

[randy700]

1. if k is negative and l is negative then return the diff as a positive number
2. if k is negative and l is positive then return a negative number
3. if k and l are positive return the diff as a positive number
4. if k is positive and l is negative return diff as a positive number.

With the formula I provided, you will always find the difference between L15 and K15.
In scenario #2 above, you will multiply that difference by -1 to produce a negative result.

Using the ABS function, it makes no difference which value is subtracted from the other.
Because the ABS function returns the value WITHOUT IT'S SIGN.

Example: L15 = 10, K15 = 2
[pre]
10 - 2 = +8 and ABS(10 - 2) = 8
2 - 10 = -8 and ABS(2 - 10) = 8[/pre]

Randy

 

[MaryMaryMorrison]

got it! Thanks so much for your help!