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

[Jamie1108]

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

 

[RickXL]

Hi,

You can do this:

=B1-SIGN(B1)*A1

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

 

[Jamie1108]

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!!

 

[RickXL]

OK, try this instead:

=B1-SIGN(A1+B1)*A1

 

[Jamie1108]

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

 

[RickXL]

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.

Thanks for your kind words.

Regards,

 

[Jamie1108]

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