Subtract Positive and Negative Values (+/- numbers in same column)

[flare9x]

Hello

This is what i am trying to achieve:

In column: AU I have the net total of various numbers. Sometimes the net number is positive other times negative.

I want to know the difference between Column AT and AS.....

So this is what I have tried:

=AS-AT

Problem with this: if AT is a negative number and AS is positive it adds to make a negative... Not what im looking for, the answer should be: -8.70.

AS AT AU (net)

1.98 -10.68 -12.66 -1.06 2.20 -3.26 9.53 -3.07 6.46 6.07 0.18 5.89 3.90 1.21 2.69 3.79 -0.35 3.44 -5.75 0.22 -5.97 1.29 4.96 -3.67 -9.64 -7.33 -16.97 11.22 4.74 6.48 -0.04 1.86 -1.90 7.02 -5.71 1.31 3.47 4.06 -0.59 -1.91 -1.78 -3.69 4.69 1.41 3.28 3.65 -2.74 0.91 -0.11 -3.75 -3.86 6.09 -1.29 4.80 -8.28 -3.91 -12.19 5.57 3.00 2.57 5.29 4.47 0.82 0.88 7.12 -6.24 -0.89 -1.60 -2.49 0.79 2.73 -1.94 <colgroup><col width="64" span="2" style="width:48pt"><col width="64" style="width:48pt"></colgroup><tbody> </tbody>

<colgroup><col width="64" span="2" style="width:48pt"><col width="64" style="width:48pt"></colgroup><tbody>
</tbody>

I then try this: =AS2-ABS(AT2)
Problem: This works with AT being negative number and AS being positive... the result is correct at -8.70.
However the problem then goes on IF: AS is negative value and AT positive.... the numbers are added to create -3.26 which is errorsome, the correct value should be 1.14

AS AT AU (Net)

1.98	-10.68	-8.70
-1.06	2.20	-3.26
9.53	-3.07	6.46
6.07	0.18	5.89
3.90	1.21	2.69
3.79	-0.35	3.44
-5.75	0.22	-5.97
1.29	4.96	-3.67
-9.64	-7.33	-16.97
11.22	4.74	6.48
-0.04	1.86	-1.90
7.02	-5.71	1.31
3.47	4.06	-0.59
-1.91	-1.78	-3.69
4.69	1.41	3.28
3.65	-2.74	0.91
-0.11	-3.75	-3.86
6.09	-1.29	4.80
-8.28	-3.91	-12.19
5.57	3.00	2.57
5.29	4.47	0.82
0.88	7.12	-6.24
-0.89	-1.60	-2.49
0.79	2.73	-1.94

<colgroup><col width="64" span="2" style="width:48pt"><col width="64" style="width:48pt"></colgroup><tbody>
</tbody>

I then try this: =AT2-ABS(AS2)
Problem with this: If AT2 is negative, then it adds the positive to make a deeper negative: -12.66. However, this works when the AS column is a negative value.... the formula is correct and gives correct value of 1.14.

1st hour	Last Hour	Net
1.98	-10.68	-12.66
-1.06	2.20	1.14
9.53	-3.07	-12.60
6.07	0.18	-5.89
3.90	1.21	-2.69
3.79	-0.35	-4.14
-5.75	0.22	-5.53
1.29	4.96	3.67
-9.64	-7.33	-16.97
11.22	4.74	-6.48
-0.04	1.86	1.82
7.02	-5.71	-12.73
3.47	4.06	0.59
-1.91	-1.78	-3.69
4.69	1.41	-3.28
3.65	-2.74	-6.39
-0.11	-3.75	-3.86
6.09	-1.29	-7.38
-8.28	-3.91	-12.19
5.57	3.00	-2.57
5.29	4.47	-0.82
0.88	7.12	6.24
-0.89	-1.60	-2.49
0.79	2.73	1.94

<colgroup><col width="64" span="2" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

Has anyone got the answer? Im thinking along the lines of
=if AS is >0 value and AT<0 value then =AS2-ABS(AT2) IF NOT then... =AT2-ABS(AS2)

Thanks for any help

 

[Joe4]

I want to know the difference between Column AT and AS.....

So this is what I have tried:

=AS-AT

Problem with this: if AT is a negative number and AS is positive it adds to make a negative... Not what im looking for, the answer should be: -8.70.

Can you explain in plain English the logic? Is a negative number really not a negative number (or least for the purposes of what you are trying to do)?

Because in pure mathematical terms,
=AS - AT
is correct and -12.66 is the expected result (and correct, mathematically).

 

[flare9x]

Im only interestd in the difference between AT and AS so its important that if AS is a positive value that this the net result

1.98

-10.68

-8.70

<tbody>
</tbody>

etc...

 

[Joe4]

So, how about:
=ABS(AS2)-ABS(AT2)

 

[SpillerBD]

I see you reference "1st Hour" and "Last Hour" in one of your tables.
Is there a time component involved in these values? If so what.

In strictest terms the difference between two numbers has no sign.
The difference From a number to another number does. It has a vector or direction.

 

[flare9x]

I see you reference "1st Hour" and "Last Hour" in one of your tables.
Is there a time component involved in these values? If so what.

In strictest terms the difference between two numbers has no sign.
The difference From a number to another number does. It has a vector or direction.

Ok I am going to plot the last hour column into a chart.
Im trying to measure the change in the last hour and compare it to the change in the first hour. A positive number would then mean that the last hour outperformed the fisrt hour.

 

[Joe4]

So, did my last suggestion do what you need?

 

[flare9x]

It works for when AT is a negative value. However, if AS i negative and AT positive, it doesnt work, thanks for helping

 

[SpillerBD]

=AS + AT
AT's sign directs the vector....

 

[Joe4]

It works for when AT is a negative value. However, if AS i negative and AT positive, it doesnt work, thanks for helping

Maybe:
=IF(AT2>AS2,ABS(AT2)-ABS(AS2),ABS(AS2)-ABS(AT2))


Edit: looks like SpillerBD may be understanding what you are after, and it is not as complex as it is being made out to be!