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

F

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
 
Sort by date Sort by votes
Joe4 said:
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!
Click to expand...
Or more simply...

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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
You must log in or register to reply here.

Similar threads

S
  • Question Question
Finding matching positive and negative numbers
Replies
5
Views
27K
starheartbeam
S
V
  • Question Question
calculating negative and positive numbers
Replies
4
Views
452
XOR LX
XOR LX
T
  • Question Question
Copy value from column A if value in column B at same position is X
Replies
4
Views
439
torsten
T
A
  • Question Question
Trying to find percentage of winners and losers
Replies
5
Views
739
tybaltlives
T
J
  • Question Question
Macro to highlight Positive and Negative Numbers
2 3
Replies
22
Views
9K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

Share this page

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