The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,531 other followers

When you wished that Excel had short-circuit boolean evaluation: Excel IF, OR and SEARCH function combination

Posted by jpluimers on 2012/09/04

I had a function like this in Excel:

=IF(OR(B2=""; SEARCH(C2;B2)); "ok"; "INCONSISTENT")

The situation should be OK when either:

  • Cell B2 is empty (an empty cell is considered equal to a zero length string).
  • One or more occurrences of the value in cell C2 can be found in the value of cell B2

Since most of my software development is outside of Excel, I’m used to short-circuit boolean evaluation.

Not with Excel: the OR is a function, not an operator, so all other function calls will be evaluated.

Since SEARCH will return #VALUE! when the first argument does not have a value, and #VALUE! propagates in a similar way as NULL in SQL does (SQL has the Boolean datatype inconsistency though).

So you need to get rid of #VALUE! as soon as you can using the one of the IS functions like the ISERROR function or the ISNUMBER function.

ISNUMBER, contrary to popular belief, not only distinguishes numbers from text, but in fact from any non-numeric value as Glaswegian kindly explained on the TechSupport forum:

Excel IF and SEARCH function combination

I am trying to do the following:

If cell A1 contains the characters “ABC” anywhere in the string or value, then “Y”, else “N”. I almost have it by using =if((search(“ABC”,A1)),”Y”,”N”). However, with the “else” if “ABC” is not found, it returns #VALUE! as opposed to “N”.

[…]
Try

=IF(ISNUMBER(SEARCH("abc",A1,1)),"Y","N")

ISNUMBER checks the type of value and returns TRUE or FALSE. The #VALUE error refers to the fact that Excel could not translate the value to the type required.

So the code ends up as this:

=IF(OR(B2=""; ISNUMBER(SEARCH(C2;B2))); "ok"; "INCONSISTENT")

–jeroen

via: Excel IF and SEARCH function combination – Tech Support Forum.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: