Excel: nested if with and/or

Question

I would like to 'translate' in excel the following condition:

if((A<0 & B<0) OR (B>= 0 & A >=0), ABS(B-A),A-B)

I have tried as follows:

if(or(and(A1<0, B1<0),(B1>=0, A1 >=0), ABS(B1-A1), A1-B1)

but it does not work.

Do you know why?

You can imagine the following as dataset

A   B

-0.2  -0.3
0.2   0.1
0.1   -0.2

BigBen · Accepted Answer · 2020-09-15 13:30:19Z

1

You're missing an AND:

=IF(OR(AND(A1<0,B1<0),AND(A1>=0,B1>=0)),ABS(B1-A1),A1-B1)

answered Sep 15, 2020 at 13:30

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user13623188 Over a year ago

In case of "." values in one of the two columns or in both, how should I change the code? I tried to add the condition in the AND but it gave me VALUE! error. I am going to mark the question in one minute

user13623188 Over a year ago

IF(OR(AND(A1<0,B1<0, AND(A1<>".",B1<>"."),AND(A1>=0,B1>=0,AND(A1<>".",B1<>".")),ABS(B1-A1),A1-B1)

BigBen Over a year ago

To test if a number contains a decimal, you should do =INT(A1)<>A1. If I understand what you're trying to do, =IF(AND(AND(A1=INT(A1),B1=INT(B1)),OR(AND(A1<0,B1<0),AND(A1>=0,B1>=0))),ABS(B1-A1),A1-B1).

JvdV · Accepted Answer · 2020-09-15 13:43:35Z

1

Little alternative:

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

Or rather:

=IF(COUNTIF(A1:B1,"<0")=1,A1-B1,ABS(B1-A1))

answered Sep 15, 2020 at 13:43

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Collectives™ on Stack Overflow

Excel: nested if with and/or

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related