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

flare9x

Board Regular
Joined
Jan 29, 2016
Messages
120
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)
[TABLE="width: 192"]
<colgroup><col width="64" span="2" style="width:48pt"><col width="64" style="width:48pt"></colgroup><tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"][TABLE="width: 192"]
<colgroup><col width="64" span="2" style="width:48pt"><col width="64" style="width:48pt"></colgroup><tbody> [TR]
[TD="class: xl65, align: right"]1.98[/TD]
[TD="class: xl65, align: right"]-10.68[/TD]
[TD="class: xl65, align: right"]-12.66[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-1.06[/TD]
[TD="class: xl65, align: right"]2.20[/TD]
[TD="class: xl65, align: right"]-3.26[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9.53[/TD]
[TD="class: xl65, align: right"]-3.07[/TD]
[TD="class: xl65, align: right"]6.46[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6.07[/TD]
[TD="class: xl65, align: right"]0.18[/TD]
[TD="class: xl65, align: right"]5.89[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3.90[/TD]
[TD="class: xl65, align: right"]1.21[/TD]
[TD="class: xl65, align: right"]2.69[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3.79[/TD]
[TD="class: xl65, align: right"]-0.35[/TD]
[TD="class: xl65, align: right"]3.44[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-5.75[/TD]
[TD="class: xl65, align: right"]0.22[/TD]
[TD="class: xl65, align: right"]-5.97[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1.29[/TD]
[TD="class: xl65, align: right"]4.96[/TD]
[TD="class: xl65, align: right"]-3.67[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-9.64[/TD]
[TD="class: xl65, align: right"]-7.33[/TD]
[TD="class: xl65, align: right"]-16.97[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]11.22[/TD]
[TD="class: xl65, align: right"]4.74[/TD]
[TD="class: xl65, align: right"]6.48[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-0.04[/TD]
[TD="class: xl65, align: right"]1.86[/TD]
[TD="class: xl65, align: right"]-1.90[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7.02[/TD]
[TD="class: xl65, align: right"]-5.71[/TD]
[TD="class: xl65, align: right"]1.31[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3.47[/TD]
[TD="class: xl65, align: right"]4.06[/TD]
[TD="class: xl65, align: right"]-0.59[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-1.91[/TD]
[TD="class: xl65, align: right"]-1.78[/TD]
[TD="class: xl65, align: right"]-3.69[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4.69[/TD]
[TD="class: xl65, align: right"]1.41[/TD]
[TD="class: xl65, align: right"]3.28[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3.65[/TD]
[TD="class: xl65, align: right"]-2.74[/TD]
[TD="class: xl65, align: right"]0.91[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-0.11[/TD]
[TD="class: xl65, align: right"]-3.75[/TD]
[TD="class: xl65, align: right"]-3.86[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6.09[/TD]
[TD="class: xl65, align: right"]-1.29[/TD]
[TD="class: xl65, align: right"]4.80[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-8.28[/TD]
[TD="class: xl65, align: right"]-3.91[/TD]
[TD="class: xl65, align: right"]-12.19[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5.57[/TD]
[TD="class: xl65, align: right"]3.00[/TD]
[TD="class: xl65, align: right"]2.57[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5.29[/TD]
[TD="class: xl65, align: right"]4.47[/TD]
[TD="class: xl65, align: right"]0.82[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.88[/TD]
[TD="class: xl65, align: right"]7.12[/TD]
[TD="class: xl65, align: right"]-6.24[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-0.89[/TD]
[TD="class: xl65, align: right"]-1.60[/TD]
[TD="class: xl65, align: right"]-2.49[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.79[/TD]
[TD="class: xl65, align: right"]2.73[/TD]
[TD="class: xl65, align: right"]-1.94[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
</tbody>[/TABLE]


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


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.

[TABLE="width: 192"]
<colgroup><col width="64" span="2" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]1st hour [/TD]
[TD="class: xl65, width: 64"]Last Hour[/TD]
[TD="width: 64"]Net[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1.98[/TD]
[TD="class: xl65, align: right"]-10.68[/TD]
[TD="class: xl65, align: right"]-12.66[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-1.06[/TD]
[TD="class: xl65, align: right"]2.20[/TD]
[TD="class: xl65, align: right"]1.14[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9.53[/TD]
[TD="class: xl65, align: right"]-3.07[/TD]
[TD="class: xl65, align: right"]-12.60[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6.07[/TD]
[TD="class: xl65, align: right"]0.18[/TD]
[TD="class: xl65, align: right"]-5.89[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3.90[/TD]
[TD="class: xl65, align: right"]1.21[/TD]
[TD="class: xl65, align: right"]-2.69[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3.79[/TD]
[TD="class: xl65, align: right"]-0.35[/TD]
[TD="class: xl65, align: right"]-4.14[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-5.75[/TD]
[TD="class: xl65, align: right"]0.22[/TD]
[TD="class: xl65, align: right"]-5.53[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1.29[/TD]
[TD="class: xl65, align: right"]4.96[/TD]
[TD="class: xl65, align: right"]3.67[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-9.64[/TD]
[TD="class: xl65, align: right"]-7.33[/TD]
[TD="class: xl65, align: right"]-16.97[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]11.22[/TD]
[TD="class: xl65, align: right"]4.74[/TD]
[TD="class: xl65, align: right"]-6.48[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-0.04[/TD]
[TD="class: xl65, align: right"]1.86[/TD]
[TD="class: xl65, align: right"]1.82[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7.02[/TD]
[TD="class: xl65, align: right"]-5.71[/TD]
[TD="class: xl65, align: right"]-12.73[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3.47[/TD]
[TD="class: xl65, align: right"]4.06[/TD]
[TD="class: xl65, align: right"]0.59[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-1.91[/TD]
[TD="class: xl65, align: right"]-1.78[/TD]
[TD="class: xl65, align: right"]-3.69[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4.69[/TD]
[TD="class: xl65, align: right"]1.41[/TD]
[TD="class: xl65, align: right"]-3.28[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3.65[/TD]
[TD="class: xl65, align: right"]-2.74[/TD]
[TD="class: xl65, align: right"]-6.39[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-0.11[/TD]
[TD="class: xl65, align: right"]-3.75[/TD]
[TD="class: xl65, align: right"]-3.86[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6.09[/TD]
[TD="class: xl65, align: right"]-1.29[/TD]
[TD="class: xl65, align: right"]-7.38[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-8.28[/TD]
[TD="class: xl65, align: right"]-3.91[/TD]
[TD="class: xl65, align: right"]-12.19[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5.57[/TD]
[TD="class: xl65, align: right"]3.00[/TD]
[TD="class: xl65, align: right"]-2.57[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5.29[/TD]
[TD="class: xl65, align: right"]4.47[/TD]
[TD="class: xl65, align: right"]-0.82[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.88[/TD]
[TD="class: xl65, align: right"]7.12[/TD]
[TD="class: xl65, align: right"]6.24[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-0.89[/TD]
[TD="class: xl65, align: right"]-1.60[/TD]
[TD="class: xl65, align: right"]-2.49[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.79[/TD]
[TD="class: xl65, align: right"]2.73[/TD]
[TD="class: xl65, align: right"]1.94[/TD]
[/TR]
</tbody>[/TABLE]



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
 
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!
Or more simply...

=ABS(ABS(AS2)-ABS(AT2))
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
It's kind of hard to tell what you want, because you only corrected the results of the first 2 in your table, but does this do what you want?

=-(-AT2-AU2)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top