Difference in whole numbers

dr427

New Member
Joined
Mar 4, 2006
Messages
22
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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