# Difference in whole numbers

#### dr427

##### New Member
I have searched and searched all day for this answer and cannot find it.

I simply want the following:

Column A Column B = Column C
-\$5 \$6 = \$1
\$8 \$2 = \$6
-\$2 \$5 = \$3
\$5 -\$5 = \$0

See my pattern? ABS won't work because for example you end up with 11 on the first one. I don't want percent difference either. A1-B1=C1 won't work because sometimes A is positive and sometimes B is positive. Nor the reverse of A1+B1=C1 it will work only when the order of numbers is correct. I am trying to avoid doing each row individually.

This seems so simple, but is killing me. Basically column A is estimated and column B is actual and I want to see the difference on these projects.

Thank you Thank you Thank you for any help! Again I have searched and searched and either don't know the correct terms or it is so simple people haven't asked it or...??? I see close topics, but nothing quite like this.

I searched "sum negative positive", "find difference", etc...

I hope I am not wasting anyone's time, but I really can't figure this out.

Last edited:

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### VoG

##### Legend
Is this the result that you want?

<b>Sheet4</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">8</td><td style="text-align:right; ">2</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">-2</td><td style="text-align:right; ">5</td><td style="text-align:right; ">7</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">5</td><td style="text-align:right; ">-5</td><td style="text-align:right; ">10</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=ABS(B1-A1)</td></tr><tr><td >C2</td><td >=ABS(B2-A2)</td></tr><tr><td >C3</td><td >=ABS(B3-A3)</td></tr><tr><td >C4</td><td >=ABS(B4-A4)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

#### dr427

##### New Member
Close, but your rows 3 and 4 aren't the difference I am looking for. The negatives have been my problem all along. Below I manually made it work just to show you the results I desire..
Book1.xls
ABCD
1ProjectedActualDifference
2-561
3826
4-253
55-50
610
Sheet1

Last edited:

#### VoG

##### Legend
Does this do it?

<b>Sheet4</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">-5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">8</td><td style="text-align:right; ">2</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">-2</td><td style="text-align:right; ">5</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">5</td><td style="text-align:right; ">-5</td><td style="text-align:right; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C6</td><td >=ABS(ABS<span style=' color:008000; '>(B6)</span>-ABS<span style=' color:008000; '>(A6)</span>)</td></tr><tr><td >C7</td><td >=ABS(ABS<span style=' color:008000; '>(B7)</span>-ABS<span style=' color:008000; '>(A7)</span>)</td></tr><tr><td >C8</td><td >=ABS(ABS<span style=' color:008000; '>(B8)</span>-ABS<span style=' color:008000; '>(A8)</span>)</td></tr><tr><td >C9</td><td >=ABS(ABS<span style=' color:008000; '>(B9)</span>-ABS<span style=' color:008000; '>(A9)</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

#### dr427

##### New Member
Close, very close! It was working until I threw in a variable. Look at row 5 I just added. I thought for a minute what if the difference should be negative?
Book1.xls
ABCD
1ProjectedActualDifference
2-561
3826
4-253
5-112-9
65-50
71
Sheet1
Book1.xls
ABCD
1ProjectedActualDifference
2-561
3826
4-253
5-1129
65-50
719
Sheet1

#### VoG

##### Legend
Now you have lost me!

A2 = -5, B2 = 6 and you want to return +1

A5 = -11, B5 = 2 and you want to return -9

What is the logic?

#### dr427

##### New Member
You are right. I have been working on this too long today!

Thank you for your help. Column A would never be -11 for this application.

Ohhhh what a day! Thank you again for your expert help! It was a blessing!

This formula works as I need, but I guess in my mind I was thinking:

-5 + 6 = 1 (Your formula works for this)

-11 + 2 = -9 (Formula doesn't work for this)

Last edited:

#### tusharm

##### MrExcel MVP
Use the untested MAX(ABS(A1),ABS(B1))-MIN(ABS(A1),ABS(B1))
You are right. I have been working on this too long today!

Thank you for your help. Column A would never be -11 for this application.

Ohhhh what a day! Thank you again for your expert help! It was a blessing!

This formula works as I need, but I guess in my mind I was thinking:

-5 + 6 = 1 (Your formula works for this)

-11 + 2 = -9 (Formula doesn't work for this)

Replies
1
Views
2K
Replies
1
Views
768
Replies
4
Views
449
Replies
3
Views
987
Replies
9
Views
344

1,190,913
Messages
5,983,536
Members
439,848
Latest member
timmyo

### 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.

### Which adblocker are you using?

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

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