If This, Then That
The early days of artificial intelligence promised much. In 1987, Lotus unveiled HAL (named after the maniacal computer in Kubrick’s 2001, a letter off from IBM) that used a natural language interface to communicate with users. The Chicago Tribune reported, “Type ‘graph this’ and you get a graph.” Twenty six years later I tell my phone, “Show my pizza!” to get a list of local restaurants with phone numbers, maps, and reviews.
But as Tron’s protagonist asks a whimsical bit within the mind of a computer, “Is that all you can say?” Computers only do what we tell them. An archetypal (and useful) concept is the If statement:
if(this is true, do this, otherwise do this)
This works in all spreadsheets. The logical test is anything that results in zero (false) or non-zero (true); often this is common sense. For example, a column can be added to your department schedule suggesting employees to review by counting the number of shifts and flagging “Review” if there is a problem, e.g.
if(count(B2:AF2)<23, “Review!”, “Ok”)
The count() function tallies cells that contain numbers (number of days worked) within the specified range. But it can be parsed by week, too:
if(count(B2:H2)<5, “Review week 1!”,if(count(I2:O2)<5, “Review week 2!”,if(count(P2:V2)<5, “Review week 3!”,if(count(W2:AB2)<5, “Review week 4!”,if(count(B2:AF2)<23, “Review week 5!”,”Ok”)))))
These If statements are nested e.g. if(this is true, do this, otherwise if(etc.)) This kind of logic is handy in building spreadsheets that manipulate, flag, and even analyze data on the fly. I probably use the If statement more than anything else in Excel or, indeed, any programming.
A website called IFTTT (If This, Then That) brings this logic alive with “recipes” that execute commands conditionally, such as sending you a text message if it’s going to snow tomorrow. That way, you don’t have to open the pod bay doors to check the weather.
NEXT: Don’t Burn Bridges