The Wiert Corner – irregular stream of stuff

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

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming

    20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now

    20140424-Windows-7-free-disk-space

    More Photos
  • Pages

  • All categories

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

    Join 1,692 other followers

Archive for September 4th, 2012

Help Test RAD Studio XE3 Support in GExperts 1.37 Beta 1 | GExperts

Posted by jpluimers on 2012/09/04

Didn’t see this one on DelphiFeeds yet:

Help Test RAD Studio XE3 Support in GExperts 1.37 Beta 1 | GExperts.

Now please Thomas, can you create an experimental build too?

–jeroen

Posted in Delphi, Delphi XE3, Development, Software Development | 2 Comments »

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.

Posted in Development, Excel, Office, Power User, Software Development | Leave a Comment »

 
%d bloggers like this: