Logical Functions in Excel Part II – AND and OR

Having represented this site as a technology blog with a focus on programming and software development, I find myself starting off with a tutorial on Excel basics instead. Somehow, I thought it was going to be more glamorous than this . . . This is driven by my need to prepare a training for my staff at work, and in keeping with the DRY principle (Don't Repeat Yourself) I am killing two birds with one stone. Anyone hoping to read programming stuff is just going to have to wait . . .

If you do not have Microsoft Excel on your machine, or if you are using an older version (I use Excel 2010 throughout this series) you might consider purchasing a copy. You can find many deals on-line, or even support my efforts with this site by using the links above to buy directly from Microsoft. I STRONGLY recommend against purchasing software from Craigslist or EBay. There is significant risk of purchasing illegal or pirated software from those sites.

Picking up where we left off . . .

In a previous post we examined the logical IF function, and the basics of its use within MS Excel. The close cousins of logical IF are logical AND and Logical OR. This group of functions is one of the more powerful features of Excel, but getting used to the absolute way the machine handles logical processing can be frustrating at first if you are not accustomed to the special meaning associated with IF, AND, and OR in the binary world of computing.

A little background for the uninitiated

Logical functions such as employed by MS Excel and indeed, most computer processes, attach a slightly different, and more stringent meaning to IF, AND, and OR than you may be accustomed to in everyday language. Your computer, at the deepest level, only really understands two values: 1 and 0. These can be metaphorically expressed as "On" and "Off", or "Yes" and "No" or "True" and "False". However, in the processor, they are reduced to ones and zeros.

We will focus on the TRUE/FALSE paradigm here.

Logical functions rely on expressions which return a value of either TRUE or FALSE. There is no in-between, because your computer (and MS Excel) do not understand "Kind of" or "Sometimes" or "Maybe".

The Mathematical expression 2 > 1 returns a value of TRUE. The expression 2 < 1 will always return a value of FALSE. As we learned previously, a logical IF function evaluates an expression (or condition) and returns a specific value if the expression is TRUE, and a different value if the expression is FALSE. We stated this in plain language like so:

If Some Conditionis TRUE Then Do something. Otherwise Do Something Else.

But what if more than one condition needs to be met in order to Do Something instead of Doing Something Else?This is where we have to look at compound logical statements; hence, AND and OR.

This AND That - the logic of buying guitars

Logical AND evaluates two or more expressions, and only returns a value of TRUE if ALL of the expressions being evaluated ALSO return TRUE. For example, I went shopping for a new guitar a couple years ago, and I had some very specific requirements for what I wanted. I was going to buy a black Gibson Les Paul Custom with gold hardware. In evaluating guitars for potential purchase, I processed my decision like this (sub-consciously, because I am not THAT big a geek) :

The guitar must be a Gibson Les Paul

AND

The Guitar must be black

AND

The guitar must have gold hardware.

Now, there was actually more to my decision on this matter, involving price, and the condition of the candidate guitar (a man must have his toys . . .). But you can see that all three of the above conditions needed to be TRUE in order that I might consider purchasing a particular guitar.

I could re-write this like so:

IF [Guitar = Gibson Les Paul AND Color = Black AND Hardware = gold] then Buy it Otherwise Keep Looking

In this expression, each of the individual conditions within the square brackets must all be true in order for the enclosing IF function to return true. If even one of the three expressions within the square brackets is NOT true, then the IF function returns FALSE, and Johnny does NOT get to buy the guitar.

MS Excel provides the following syntax for the AND function:

=AND(condition1, condition2, condition3,  . . . etc.)

If all of the nested conditions are TRUE, then the AND function will return TRUE as well. We can try this out right now (remember to always begin your formula with an equals sign!):

Then we hit enter, and voila:

Now, in order to determine which of these classic guitars fit my criteria, I can simply copy the formula into the other cells in the "Buy it?" column:

As we can see, while there are several very attractive guitars here, there is only one which meets all three of my criteria for a purchase, as indicated by our AND function.

Les Paul OR Stratocaster? Life is full of difficult choices . . .

Now I have a problem, however. While I was looking at all these guitars, I remembered that I also really love the tone and playability of the Fender Stratocaster as well. In fact, if just the right one came along while I am out and about shopping for a Les Paul, I might just snap it up. After all, a man has GOT to have some diversity of tone, right? If I were to run into the right Les Paul OR the right Stratocaster, I will make a purchase. In a Strat, I am looking for the same things as with the Les Paul. Black, with gold hardware (OK, not really - gold hardware on a black Strat would just be . . . not right. but I need to keep the example simple here). I have now introduced an OR into my equation.

Logical or evaluates two or more expressions, and returns TRUE if one or more of the expressions return true. In other words, unlike AND, which requires ALL conditions to be true in order for the AND function itself to be true, OR requires only that at least ONE of the nested conditions are true. In my example above, if the guitar under consideration is a Gibson Les Paul OR a Fender Stratocaster, my first condition has been met. The OTHER two conditions (black, AND gold hardware) BOTH have to be true in order for me to buy the guitar. We can write this as follows:

IF { [guitar = "Gibson Les Paul" OR "Fender Stratocaster"] AND color = "Black" AND "Hardware = "gold"] } Then Buy It! Otherwise, Keep Looking.

Notice that we now have a more complex expression. We have an OR (within the square brackets) which must be true as one member of a set of three conditions (within the "curly braces") that ALL must be true.

The syntax for logical OR in MS Excel is similar to that of AND:

=OR(condition1, condition2, condition3, . . . etc.)

However, in our case, we are nesting an OR within and AND, so we would write it like THIS in Excel:

Note in the above example that the OR is nested within the and. The placement of your parentheses is important here. Forgetting to close the paren on the OR statement will really screw things up! If we hit enter, and then copy the formula to the other cells in the "Buy it?" column:

There are now two guitars which meet our criteria.

It's, um, complicated

It is possible to perform stunning leaps of logical manipulation by cleverly employing IF, AND, and OR within your spreadsheets. It takes practice though, and when you need to employ nested logical statements it often pays to map things out beforehand in pseudo-logic like we did above. Play with it, and then the nest time you find yourself wishing you could perform one calculation if this AND that OR some other thing met certain conditions, you will have a running start.

Things to love about Java: Exception Handling

In my quest to learn and become a better developer, I undertook an excursion into Java-land. Partly this was driven by the fact that Java is the language used for Android app development, and partly because it was time to branch out and explore my first non-Microsoft-driven development platform. Until sometime in 2010, I had only worked within various Microsoft languages, mostly VB and C# .NET and VBA/VB6.

As with any significant change, there was some initial frustration. However, as I became accustomed to the Java way of doing things, I discovered a few implementation gems within the language which I really liked. Chief among these was the exception handling model.

Java identifies two categories of exception: the Checked Exception, which well designed code should anticipate and handle, and Unchecked Exceptions, which arise from errors external to the system, or within the runtime execution of the program, and which are difficult to anticipate and/or handle in any practical sense.

Java REQUIRES that any method which might potentially encounter or throw a Checked Exception adhere to a Check or Specify policy. What this means is that any method which throws such an exception must specify such as part of the method signature, and that client code consuming the method must either handle the exception, or again specify that it will throw the same exception.

A Trivial Example for Comparison Part I - The C# Way:

By way of illustrating the difference between exception handling in C# and that of Java, we will create a simple library class called RentalAgreement (We are really just focusing on exception handling here, and this is a REALLY trivial example, so I don't wanna hear about problems with the business logic, or clunkiness of the examples!) A rental agreement has a start date and an end date (and some additional information, but for the purpose of brevity, we will leave our class at that for the moment).

C# Example 1 – Basic C# Code:

    public class RentalAgreement
    {
        private DateTime _startDate;
        private DateTime _endDate;
    
        publicRentalAgreement(DateTime StartDate, DateTime EndDate)
        {
            _startDate = StartDate;
            _endDate = EndDate;
        }

The constructor for this class accepts two arguments, a start date and and end date. Since our business model dictates that the end date must occur AFTER the start date, we might want to set up some exception handling to ensure a valid range between the start and end dates. Our code can then propagate an exception to any client code if such an event occurs. We'll modify our class slightly, adding a local function to compare two dates for precedence, and a if/else throw block in the constructor:

C# Example 2 – Improved C# Code:

    public class RentalAgreement
    {
        private DateTime _startDate;
        private DateTime _endDate;

        public RentalAgreement(DateTime StartDate, DateTime EndDate)
        {
            //Use local function NoPrecedence to compare the start and end dates:
            if (this.NoPrecedence(StartDate, EndDate))
            {
                _startDate = StartDate;
                _endDate = EndDate;
            }
            else
            {
                // If the end date occurs before the start date, let client 
               // code know about it:
                throw (new Exception("The end date cannot occur before the start date"));
            }
        }      

        private bool NoPrecedence(DateTime StartDate, DateTime EndDate)
        {
            if(EndDate < StartDate)
            {
                return true;
            }
            return false;
        }
    } 

The exception thrown in the constructor will propagate up the call stack to the client code, which can then implement some well-thought-out handling. Or not. It could be that our erstwhile developer might have overlooked the need to validate user input, or otherwise missed the potential exception case. In any case, the following test code mimics what might happen if a user were to enter a start date of 1/1/2011, and an end date of 12/31/2010:

C# Example 3 – Bad, BAD Client Code:

    private void button1_Click(object sender, EventArgs e)
    {
        DateTime startDate = new DateTime(2011, 1, 1);
        DateTime endDate = new DateTime(2010, 12, 31);

        RentalAgreement rentalAgreement = new RentalAgreement(startDate, endDate);
        MessageBox.Show("Start Date = " 
            + startDate.ToShortDateString() 
            + " : End Date = " + endDate.ToShortDateString());
    }

However it happened, our hapless user, on entering the above incorrect date combination, would be faced with THIS ugliness:

Of course, all of this might be averted if our developer implements some exception handling in his client code:

C# Example 4 – Much Better Client Code (kind of):

    private void button1_Click(object sender, EventArgs e)
    {
        DateTime startDate = new DateTime(2011, 1, 1);
        DateTime endDate = new DateTime(2010, 12, 31);

        try
        {
            RentalAgreement rentalAgreement = new RentalAgreement(startDate, endDate);
            MessageBox.Show("Start Date = "
                + startDate.ToShortDateString()
                + " : End Date = " + endDate.ToShortDateString());
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);            
            // Now do some stuff to reset the GUI so that the user can see exactly
            // where they screwed up . . . 
        }
    }

A Trivial Example for Comparison Part II – The Java Way

The Java version of our class differs only slightly from the C# code. If we add the throw statement to our code in the else block before we add the throws declaration to the method signature, the compiler (I am using Eclipse) warns us that our method presents an unhandled exception, and will not compile. This is the Check or Specify policy informing us that we either need to handle the exception condition within the current method, or specify in the method signature that there is potential for the exception to occur, and that client code must provide the handling mechanism. Once we add the throws declaration in the method signature, everything is fine again:

Java Example #1 – with throws keyword

    public class RentalAgreement 
    {
        private Calendar startDate;
        private Calendar endDate;
        
        // Note the throws clause of the method signature:
        public RentalAgreement(Calendar StartDate, Calendar EndDate) throws Exception
        {
            if (this.NoPrecedence(StartDate, EndDate))
            {
                startDate = StartDate;
                endDate = EndDate;            
            }
            else
            {
                // Because this method throws a checked exception, we are REQUIRED
                // to either handle the exception condition or specify in the method 
                // signature that the exception might be thrown.
                throw (new Exception(""));
            }
        }
        

        private boolean NoPrecedence(Calendar StartDate, Calendar EndDate)
        {
            if(EndDate.getTimeInMillis() < StartDate.getTimeInMillis())
            {
                return true;
            }
            return false;
        }
    }  

Now, we have defined a library class containing a method which throws a checked exception. Next, lets create another silly piece of code which consumes the class, mimicking some faulty user input:

Java Example #2 – Consuming the Method

    public static void main(String[] args)
    {
        //Mimic some user input:
        Calendar startDate = Calendar.getInstance();
        startDate.set(Calendar.YEAR, 2011);
        startDate.set(Calendar.MONTH, Calendar.JANUARY);
        startDate.set(Calendar.DAY_OF_MONTH, 1);
        
        Calendar endDate = Calendar.getInstance();
        endDate.set(Calendar.YEAR, 2010);
        endDate.set(Calendar.MONTH, Calendar.DECEMBER);
        endDate.set(Calendar.DAY_OF_MONTH, 31);
        
        //Attempt to create an instance of the RentalAgreement class:
        RentalAgreement newRentalAgreement = new RentalAgreement(startDate, endDate);
        
    }

The compiler flags our code at the point where we attempt to create an instance of the Rental Agreement class, and in fact will not compile as written. Why? Because the constructor of the RentalAgreement class posits that it might throw an exception, and we have not provided a handling or propagation mechanism for this. Our client code is REQUIRED by Java to either Check the exception (most often with a try . . .catch block) or Specify, again, that the exception may be raised by the current method, and declared as part of the method signature. Since the current code represents the application entry point, we will need to provide some graceful handling (with a try . . . catch block) of the exception before our application will even compile:

Java Example #3 – Client Code with Exception Handling:

    public static void main(String[] args)
    {
        //Mimic some user input:
        Calendar startDate = Calendar.getInstance();
        startDate.set(Calendar.YEAR, 2011);
        startDate.set(Calendar.MONTH, Calendar.JANUARY);
        startDate.set(Calendar.DAY_OF_MONTH, 1);
        
        Calendar endDate = Calendar.getInstance();
        endDate.set(Calendar.YEAR, 2010);
        endDate.set(Calendar.MONTH, Calendar.DECEMBER);
        endDate.set(Calendar.DAY_OF_MONTH, 31);
        
        //Attempt to create an instance of the RentalAgreement class:
        try
        {
            RentalAgreement newRentalAgreement = new RentalAgreement(startDate, endDate);
            SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
            System.out.print("State date = " + 
                    formatter.format(startDate.getTime()) + 
                    " : End Date = " + 
                    formatter.format(endDate.getTime()));
        }
        catch (Exception ex)
        {
            // Inform the user about the error of their ways:
            System.out.print(ex.getMessage());
        }        
    }

The code in Java Example #3 compiles and runs properly.

Note that not ALL exceptions receive this special treatment within Java. Unchecked Exceptions which derive from the java.lang.RuntimeException or java.lang.Error do NOT require adherence to the Check or Specify policy. In fact, because the requirement to build the Check or Specify mechanism into code is often viewed as a pain the ass, some Java developers tend to write code which throws RuntimeExceptions where in fact a checked exception is warranted, or derive their own Exception classes from RuntimeException in order to avoid writing a bunch of handling code and/or adding the throws clause to method signatures.

In my humble opinion, these folks are depriving themselves (and more importantly, consumers of their code) of one of the more useful benefits of the Java language architecture. Yes, it IS a pain in the ass to follow up and Check/Specify all those Checked Exceptions. But this requires us to construct better code, in which many of the exceptional cases which should either be pinned down with proper handling, or eliminated through design improvements and structural code changes. This ALSO provides an informative mechanism for developers who may use our libraries in their own applications, through which they will know straight away what type of exception to expect when calling one of our methods.

I am a strong fan of C# and .NET in general. But one area where the designers of the Java language got things right was in requiring such handling of exceptions, and the Check/Specify policy.

Whose Idea was This, Anyway?

An Opinionated Rant About the State of the US patent System

Whether or not you are a software developer, smart phone manufacturer, or purveyor of touch-enabled devices (just to name a few), you have no doubt heard the back and forth within the technology sector about patent infringement. Further, you are most likely aware of the "sweeping reforms", otherwise known as the America Invents Act, passed into law  by the US government in September. Chief among the "reforms" of the patent system in the US is a game-changing shift from a "first-to-invent" policy to one based on "first-to-file" when it comes to establishing ownership of an invention (which includes intellectual property, such as software and ideas).

What's a patent, and why do I care?

From the American Heritage Dictionary:

1. The exclusive right granted by a government to an inventor to manufacture, use, or sell an invention for a certain number of years;
2. an invention or process protected by this right.
3. an official document conferring such a right.

In the simplest sense, a patent protects the right of an inventor to realize the fruits of his or her efforts, while also providing an incentive for investment in innovation. In the United States, the concept of the patent is established in Article I Section 8 of the US Constitution, empowering the Congress "To promote the Progress of Science and useful Arts, by securing for limited Times to Authors and Inventors the exclusive Right to their respective Writings and Discoveries"

Note the actual intent of this statement. The purpose of the patent system is the promotion of progress/innovation. The protections afforded inventors are the means by which this progress is achieved.

My first bitch: The game is rigged

Under prevailing US patent law, the patent holder can claim infringement at any time during the term of the patent. Further, it is the obligation of the "other inventor" to ascertain that an idea, invention, or intellectual property does not infringe upon the patent of another party. While patents are made public once granted by the US patent office, the sheer number of patents to be searched is staggering. Moreover, patent searches (and the additional legal legwork required to determine the risk of a new invention infringing upon an existing patent) is expensive and complex. Frightfully so.

Large corporations have armies of lawyers (who often have degrees in engineering in addition to their law degree) to handle the patent process. Even then, most of the time the best that can be hoped for is that this army of lawyer/engineers are able to determine that there is an "acceptable risk" that a product will not be infringing upon an existing patent. 

Small companies, startups, and independent developers rarely have the resources to perform this type of exhaustive search.

Making matters worse, there were, in 2008, 6.6 patents pending for every patent issued. According to the US Patent and Trademark Office (USPTO), it takes about 22 months to obtain a patent. Given that 22 months is longer than the development cycle for many technology companies, it is possible for Company A to begin independently developing a software or hardware product before patent records related to another entity's patent claim are even available. Company A might incur substantial costs for development of the soon-to-be-patented idea, followed by substantial investment in creating and developing a market. The newly-minter patent-holder is able to wait until Company A achieves significant market success with the new product before filing suit claiming patent infringement.

My Second Bitch: Who approves these things, anyway?

Even worse, recent patent litigation seems to be based upon hopelessly broad and/or common-sense ideas, duly committed to paper at the USPTO, under which nearly any software product might be accused of infringement. It would seem that what one needs in order to secure an enforceable patent at the USPTO is not an original, innovative "invention" but instead the financial and legal wherewithal to create reams of paperwork documenting generic ideas and making them sound original. 

As an example, let's examine US Patent # 5,251,294 "Accessing, assembling, and using bodies of information. This patent, filed in 1993 and now part of the Lodsys portfolio, opens with the following abstract:

"An interactive information environment for accessing, controlling, and using information. Using a computer, available sources of information are accessed, and components are extracted, labeled, and formed into discrete units called contexts. A user selects and rearranges context labels and their associated contents. Contexts are selected and combined into new information structures called alternates, which are combinable with contexts into preferred situations. The preferred situations in turn are combinable with the foregoing components into meta-situations. All components have labels; labels and their associated contents are interchangeably movable and copyable at the levels of these information structures, whether they are located locally or remotely, and the information structures are combinable . . ." blah blah blah.

Does this sound like any common data organization models YOU know? Depending upon who you are, you might claim that nearly any spreadsheet program, relational database (and/or its associated GUI), HFS file system, HTML-based web page, or other computer usage of graphical data representations violates this patent.

The recent furor around infringement claims by Lodsys related to "in-app purchasing" find their basis in a similarly nebulous and far-reaching patent, US Patent #7,222,078 "Methods and systems for gathering information from units of a commodity" . . .

This patent opens with THIS abstract:

In an exemplary system, information is received at a central location from different units of a commodity. The information is generated from two-way local interactions between users of the different units of the commodity and a user interface in the different units of the commodity. The interactions elicit from respective users their perceptions of the commodity.

Wow. This could ALSO cover most software ever written.

In both cases, the "inventions" are the product of Mr. Dan Abelow, who seems to sit around creating drawings and narratives of hopelessly broad technology concepts and then patenting them. Lodsys, which is in reality a holding company for Mr. Abelow's patents, seems to exists for the primary purpose of "enforcing" those patents once some company achieves profitability from a technology implementation which might be said to utilize them.

Under this patent system, it seems like one might make a lucrative career out of dreaming up documentation for common uses of existing (and more importantly, existing but not-yet-fully-realized) technology, and filing your "inventions" with the USPTO. Then, just sit around and wait for someone ELSE to make the investment in bringing your "idea" to fruition. The, SUE!

How do Lodsys, Mr. Abelow, and other patent "trolls" pull this off? Why would large companies enter into licensing agreements for such nebulous patents? Because it is less costly to do so than to litigate in court. Also because most of these large companies have their OWN portfolios of patents, mostly (but not always) held as a defensive measure against just this sort of litigation.

Unlike the Patent "troll" large companies hold their patent portfolios for a number of reasons:

• To protect legitimate innovation and to recover the costs of R & D, product development, etc. related to brining an innovation to market
• To hedge against litigation as mentioned previously.
• To use against competitors in restraint of competition.

Of these three, it is this last which is the most troubling to me. Say what you want about the Big Evil Corporation (BEC). If BEC creates an innovative technology, the patent concept is properly used in protecting the investment in developing the product, and incentivizing such innovation. But when said BEC attempts to use broad and what I will call "shady" infringement accusations as a means of restraining competition, I feel we enter a different realm. This is the mark of a company which cannot compete on the merits of its product, and which seeks instead to either reap profit or avoid competing by forcing its competitor to license dubious patent rights instead.

Is THIS what the Patent System is for?

I am certain this is NOT what our founding fathers intended when they empowered the Congress to "Promote the Progress of Science and useful Arts, by securing for limited Times to Authors and Inventors the exclusive Right to their respective Writings and Discoveries."

What do you think?

Some interesting resources on this topic:

• U.S. Patent and Trademark Office (USPTO) - Official Site
• USPTO FAQ Page
• Dan Abelow's "Inventions"
• Lodsys, patents, and Apple: what's going on here - article by Nilay Patel for The Verge
• US Patent #7,222,078 – the "In-App purchasing" patent held by Lodsys
• US Patent #5,251,294 - "Accessing, assembling, and using bodies of information"

Logical Functions in MS Excel: IF, AND, and OR

Having represented this site as a technology blog with a focus on programming and software development, I find myself starting off with a tutorial on Excel basics instead. Somehow, I thought it was going to be more glamorous than this . . . This is driven by my need to prepare a training for my staff at work, and in keeping with the DRY principle (Don't Repeat Yourself) I am killing two birds with one stone. Anyone hoping to read programming stuff is just going to have to wait . . .

If you do not have Microsoft Excel on you machine, or if you are using an older version (I use Excel 2010 throughout this series) you might consider purchasing a copy. You can find many deals on-line, or even support my efforts with this site by using the links above to buy directly from Microsoft. I STRONGLY recommend against purchasing software from Craigslist or EBay. There is significant risk of purchasing illegal or pirated software from those sites.

If You're Happy and You Know It, Clap Your Hands . . .

How many times have you wished you could do something with a column of data in your spreadsheet like say:

If the value in the first cell is "Happy" then display "Clap" in the current Cell. Otherwise, display "Don't Clap"

A more concise way to put this might be:

If Cell1 = "Happy", Then display "Clap" in this cell. Otherwise, display "Don't Clap" in this cell.

To put this more generally, we might say:

If Some Condition is TRUE Then Do something. Otherwise Do Something Else.

Happily, Excel gives us a built-in function for just this purpose! But first, a quick review, for those not familiar with Excel's function syntax . . .

Recall that in Excel, you use a built-in function by writing an expression using the function by name, and providing arguments between the parentheses where they are required. Some of the arguments may be optional (meaning you CAN provide them, but if you don't Excel will use a default value instead). Arguments for a function are separated by commas.

Assistance Please?

Excel 2010 provides some assistance with using built-in functions. If you type an equals sign followed by the beginning of a function name into a cell, excel will pop up with a list of built-in functions which match the partial text you have typed. If you click on one of the functions available in the list, Excel will provide you with a brief description of what the function does:

Syntax Helper

If you select a particular function, and then type the opening paren, Excel will display a syntax helper which informs you about required and optional parameters used by that function. Required arguments are displayed in regular type, and optional arguments are displayed in regular type, but enclosed in square brackets:

A Silly Example of the IF Function

The syntax for using the IF function in MS Excel is as follows:

=IF(logical_test, [Value if True], [Value if False])

In which the first argument, Logical Test is bold. The logical_test specifies a logical (or Boolean) condition which evaluates to TRUE or FALSE. The other two arguments, [Value if True] and [Value if False] are in brackets, and in standard text, meaning they are optional. These arguments allow you to specify what your function should return for each case set forth by the logical_test.

Note that logical_test is a required argument. We must provide an expression of some sort which returns TRUE or FALSE in order for the function to work. However, we are NOT required to provide both of the other two arguments (although we DO need to provide one or the other). However, if we do not provide a value for either [value_if_true] or [value_if_false] the function will return plain old TRUE or FALSE respectively (depending upon how the logical_test evaluates), as default values.

Note that if we type the beginning of our function into a cell, the syntax helper will remind us of this:

Now, let's finish typing our function into the cell. We want to test the contents of Cell A1. If Cell A1 contains the text "Happy", we want to display the text "Clap" in the current cell. If Cell A1 contains any value OTHER than the text "Happy", we want to display "Don't Clap" in the current cell. NOTE:when we specify strings of text as arguments, we enclose the text in double quotes.

Typing the IF Function:

In the image above, our logical test begins after the opening paren, and takes the form A1 = "Happy".

• The value we wish to return if the test is TRUE, "Clap", follows the comma after our logical_test.
• The value we wish to return if the test is FALSE, "Don't Clap", follows the comma after the value_if_true argument
• Then we make sure to type the closing paren, and hit enter:

The result:

Now, select Cell B1 again, right-click and select "copy" from the context menu. Then select cell B2, and use "Paste" from your right-click menu to paste the function into cell B2. Hit enter:

Paste into the cell below and hit enter:

Congratulations! You have just created your first conditional Function in Microsoft Excel!

What is Conditional Logic?

The capability to evaluate a pre-defined logical condition and respond one specific way if the condition is true, and another way if the condition is false, is known as Conditional Logic. When we discussed Data Types in Excel, we touched briefly upon the Logical Data Type, but only to lightly cover the bare essentials.

There is an entire branch of mathematics known as Boolean Algebra which examines logical operations. We are not going to go that deep here, but it is important to recognize that there is a core set of logical operations which can be effectively viewed as mathematics of two values, TRUE and FALSE (known by your computer, at the very lowest level as the numbers 0 and 1).

For the purpose of our discussion of Conditional Logic in MS Excel, there are three logical operations: IF, AND, and OR. Each of these functions works as you might expect, so long as you remember that the computer is literal, and applies no judgment to the evaluation. Also we must remember that in evaluating logical functions, the semantics we use depart slightly from the manner we might employ these devices in natural language.

In this post, we are going to take a more detailed look at the IF function. We will expand the discussion to include AND and OR in the next post.

IF is the most basic logical function, and the easiest to understand. IF Some Condition is met the result of an IF statement will be TRUE. In all other cases, the result will be FALSE. The following are valid examples of a logical IF statement in purely mathematical terms:

• IF 1 > 2 returns FALSE
• IF (2 = (1+1)) returns TRUE

Within Excel, if we seek only to return a TRUE or FALSE, we do not need to use the IF function. We can simply test the validity of a statement be evaluating equality (or inequality) within an expression. For example, we might wish to know which sales people have met a specific sales quote for the month:

If we copy the formula above into the other cells in the column, our results look like this:

Nesting Expressions Within the IF Function

The previous example was somewhat trivial. Let's examine something a little more complex. We might wish to award a bonus to those sales people who exceed their monthly quota by a certain amount or more. We decide that those who exceed the monthly sales quota by at least 20% will receive a bonus equal to 10% of the amount over the quota monthly quota. To put our problem into English, we might say:

If the Monthly Total is at more than 20% greater than the Monthly Quota, display a bonus equal to 10% of the difference between that Total Sales and The Monthly Quota. Otherwise display zero.

From this, we can see that our Logical Test is:

Monthly Total – Monthly Quota > Monthly Quota x .20

We can also see that, if the Logical test is TRUE, we want to display the result of the following statement:

(Monthly Total – Monthly Quota) x .10

If the Logical Test is FALSE, we want to display zero (no bonus).

If we type the formula above into our spreadsheet, then copy the formula into the remaining cells in column D, we see the following results:

Clearly, our compound function worked. We were able to nest some expressions into one or both of the output conditions and return a useful result indicating the proper bonus amount for each employee.

The IF function in Microsoft Excel adds an entire new layer to the possibilities for evaluating and manipulating our data. The syntax can take a little getting used to, and it is easy to miss a closing parenthesis when nesting other expressions as value_if_true and value_if_false. But IF is a powerful tool in your arsenal when creating spreadsheets, and I consider the IF function and its cousins AND and OR to be a must-have.

In my next post we will take a look at setting up more complex conditional expressions in Excel by adding the logical functions AND and OR into the mix.

John on Google

Excel Basics Part IV: Number Formats

Having represented this site as a technology blog with a focus on programming and software development, I find myself starting off with a tutorial on Excel basics instead. Somehow, I thought it was going to be more glamorous than this . . . This is driven by my need to prepare a training for my staff at work, and in keeping with the DRY principle (Don't Repeat Yourself) I am killing two birds with one stone. Anyone hoping to read programming stuff is just going to have to wait . . .

If you do not have Microsoft Excel on you machine, or if you are using an older version (I use Excel 2010 throughout this series) you might consider purchasing a copy. You can find many deals on-line, or even support my efforts with this site by using the links above to buy directly from Microsoft. I STRONGLY recommend against purchasing software from Craigslist or EBay. There is significant risk of purchasing illegal or pirated software from those sites.

OK. If you have been following this little series, you are likely a beginning Excel User, or a glutton for punishment (or both). If you have not been following this series, but instead stumbled upon this entry, welcome to my series on Excel Basics.

We are going to step out of our examination of formulas and expressions for a moment to take a look at number formatting. While we touched on this subject very briefly in the first post, we will take a closer look now.

We will examine formatting from two perspectives. If you are an absolute beginner, you will learn how to apply formatting to cells, and a little about the various number formats themselves. If you are an intermediate or advanced user, you might find the section on the difference between the displayed format and the actual value stored in the cell to be a useful review.

For everyone’s benefit, let me put forth the following important reminder. Write this on your forehead:

It is the stored value that is used in calculations, NOT the displayed (formatted) value!

In this post

• Formatting Basics
• Applying Formatting
• The Hidden Dangers of Formatting

Formatting Basics (the VERY basics)

As we learned in the first post in this series, Microsoft Excel Basics Part II: Data Types in Excel, all number values (including dates) are stored by Excel a double-precision floating point values. How, then, do we know what is a date, what is currency, what is a percentage, and so on?

Formatting, that's how.

Excel provides us with number formatting as a means to display a numerical value in a recognizable way relative to the context of use. If we are working with monetary values, it is helpful for our spreadsheet to display our data in a currency format. Likewise with dates and percentages. Exceptionally large (or exceptionally small) numerical values are often more effectively presented using scientific notation.

Let’s take a quick look at the basic number formats Microsoft Excel Provides. In the following image, the left-most column of cells all contain the same number value (3.33). The middle column describes the number format applied to the same value typed into the cells in the right-most column :

Applying Formatting

To apply formatting to a cell or group of cells, we right-click within the selected cell(s). Select the “Format Cells…” menu item from the context menu:

You will be presented with a dialog which allows you to select from a list of pre-defined number formats (plus a few special and user-defined ones). Selecting an item from the list of available formats on the left will cause various options for the selected format to become available in the space to the right of the list.

In this case, I am demonstrating the application of basic currency formatting. The options available for the currency format include number of decimal places, the desired currency symbol, and various means of representing negative numbers. The default values for all of these are what is displayed when you select the currency format (2 decimal places, US dollars (dependent upon your system localization settings) and the default representation for negative values. For this example, I am going with the defaults. When you hit the OK button, the 3.33 value we typed into the cell will be displayed as $3.33.

[Back to Top]

This is a good place to re-state the warning I posted in red at the beginning of this post:

It is the stored value that is used in calculations, NOT the displayed (formatted) value!

The hidden danger of number formatting in calculations

Open a new Excel Workbook. Then select the first two columns by clicking on the column headers (the gray rectangles with the letters A and B in them:

Select column A:	Right-click and select "Format Cells"

Now, do like we did in the first example, and format both columns as Currency. Use the default settings (2 decimal places, US currency, or whatever is native where you live). You will run into this format often in spreadsheet-land. Now select the first cell in column A (Cell A1), and type a simple formula to divide 10 by 3:

Enter the formula:	Hit Enter:

Then, select the first cell in column B (Cell B1) and type the number 3.33:

Enter the number 3.33:	Hit Enter:

Note that each cell now appears to contain the value of three dollars and thirty-three cents. Now we will use a logical test to illustrate why we need to be aware of how formatting can impact our spreadsheet design. We can compare the values stored in Cells A1 and B1 to see if they are equal. The following expression will return a logical result of TRUE or FALSE:

Enter the formula =A=B	Hit Enter:

Now, why is that? I will say it one more time, for clarity:

It is the stored value that is used in calculations, NOT the displayed (formatted) value!

Excel treats the formula we entered into cell A1 as a value. The actual value of dividing the number ten by three is, in mathematical terms, 3.333333333333 . . . and so on. In performing the calculation, Excel carries this out to the limits of its 15 significant digits. While we see displayed $3.33, the value represented in the cell is actually 3.33333333333333. The number we entered into cell B1 is specifically 3.33.

Is 3.33 = 3.33333333333333?

No. The value in cell A1 is greater than the value in B1 by a little more than three one-thousandths. In other words, 3.33333333333333 > 3.33, and therefore, the expression testing whether or not the two values are equal returns FALSE.

A Side Note

If you select both columns again and remove the formatting (by setting the format back to "general") you will see that the number in cell A1 is displayed as 3.3333333333, which is only 11 significant digits. However, if you select the cell and look in the formula bar, you will see all 15 significant digits. Again, what is displayed in the cell does not represent the exact value stored in the cell.

Be aware of numerical precision and rounding in ALL your formulas

When using Excel, it is important to bear in mind that number formatting can obscure the mathematical precision of values. This is especially true of calculations involving division, but can also apply to other operations as well. Most of the time, this will have little impact, particularly when dealing with currency values in the context of everyday business transactions.

However, in certain calculations, precision which is masked by formatting can introduce errors (usually rounding errors or comparison errors such as illustrated here) which might invalidate your worksheet. Equally as often, this type of error is not immediately apparent.

Formatting is our friend, and it present our data in a familiar context which makes for visually appealing spreadsheets. Best, however, to pay attention to results which appear not to make sense. There is likely a reason for that.

[Back to Top]

In another post, we will examine rounding and rounding errors, and how these can impact your spreadsheets.

John on Google