IF AND OR FORMULA

[joanne10312]

Hello, my problem is this:
two cells that need to be added or subtracted. if cell 1 is a (-) and cell 2 is (+) then cell 1 + cell 2, or if cell 1 is a (-) and cell 2 is a (-), then cell 1 - cell 2, or if cell 1 is a (+) and cell 2 is a (+) then cell 1 - cell 2, if cell 1 is a (+) and cell 2 (-) then cell 1 + cell 2.

thank you!!!

 

[Mark W.]

{=IF(SUM((A1:B1<0)+0)=1,A1+B1,A1-B1)}

where A1 is cell 1 and B1 is cell 2.

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-10-30 13:19

 

[lenze]

Here's a non array approach

=IF(ISEVEN(COUNTIF(A1:B1,"<"&0)),A1-B1,A1+B1)

 

[Mark W.]

On 2002-10-30 13:19, lenze wrote:
Here's a non array approach

=IF(ISEVEN(COUNTIF(A1:B1,"<"&0)),A1-B1,A1+B1)

Or...

=IF(ISEVEN(COUNTIF(A1:B1,"<0")),A1-B1,A1+B1)

 

[Dave Patton]

for variety

=IF(OR(AND(A2<0,B2>0),AND(A2>0,B2<0)),A2+B2,A2-B2)