Subtract is positive and add if negative for variance of two cells.

Jamie1108

New Member
I am trying to find the variance between two cells (Actuals B1 vs Plan A1). I am subtracting …. example B1-A1. However when B1 is a negative number, it adds them. Is there a formula that will subtract if B1 is positive and add if B1 is negative?

A1 66,075
B1 -176,000
Desired Result C1 -110,725

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi,

You can do this:

=B1-SIGN(B1)*A1

It gives -109,925 it that what you meant?

Thank you ... sorry typo, I meant

A1 66,075
B1 -176,800
Desired Result C1 -110,725

The formula you mentioned works but only if A1 is less than B1. The data in my worksheet has planned \$ in column A and actual \$ in column B. In some cases we have credits applied in column B, resulting in a negative number. I am trying to find the variance for column C. Here are a couple examples:

A1 75,000 (Plan)
B1 -68,903 (Actual)
Desired Result C1 -143,903 (Since there was a credit, we would add to the plan)

another example

A1 65,323 (Plan)
B1 -195,146 (Actuals)
Desired result C1 -129,823

Hope that makes more sense. I am having trouble when my actuals are actually credits.

Any help would be greatly appreciated!!

=B1-SIGN(A1+B1)*A1

Perfect!! You're awesome RickXL .. Thank you!!!

You're awesome RickXL

It has been said. I will have to wait for my head to shrink now so I can get out of the door.

Regards,

Nah, sometimes leaving the house is overrated .... I say stay inside and enjoy a movie or something. Thanks again Rick!!

Replies
3
Views
64
Replies
2
Views
178
Replies
18
Views
436
Replies
9
Views
336
Replies
4
Views
445

1,203,558
Messages
6,056,078
Members
444,844
Latest member
Taps07

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.

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