Combining OR with AND in Excel

2

I'm trying to write a formula in Excel 2016 which outputs a "Yes" if the following two conditions are met:

  1. A2 value begins with 4 OR 9
  2. A2 value length is 8

Otherwise output "No".

This is the formula I've come up with:

=IF(AND(OR(LEFT(A2;1)="4";LEFT(A2;1)="9");LEN(A2)="8");"Yes";"No")

Problem is, it just doens't work :(

Any ideas?

igneous

Posted 2015-10-20T14:17:24.710

Reputation: 23

Answers

2

All I really saw being an issue was the length returning an integer versus a string(ie, don't use quotes for that comparison). I don't have 2016 to test on, but in 2013 this works properly:

=IF(AND(OR(LEFT(A2;1)="4";LEFT(A2;1)="9");LEN(A2)=8);"Yes";"No")

panhandel

Posted 2015-10-20T14:17:24.710

Reputation: 2 394

Semicolons in formula is a regional thing, but removing quotation marks from LEN fixed it. Thanks a bunch! – igneous – 2015-10-20T14:31:47.807

Edited to reflect semi-colon usage for your region – panhandel – 2015-10-20T14:52:40.797

Asked: 2015-10-20T14:17:24.710

Viewed: 117 times

Active: 2015-10-20T14:34:41.560