Excel Basics Consolidated

Posted on November 16 2012 09:11 AM by John Atten in Excel Basics, Education   ||   Comments (0)

I have done a series of posts on Microsoft Excel, targeted largely at beginners, and/or those who use Excel casually and seek to expand their skills a little. MS Excel is a powerful program, which has a host of advanced analytical and math functions, for those who spend a moment exploring them.

What is important about using excel to maximum effect is not remembering how to use each specific function of formula - it is enough to understand the syntax excel requires, and to realize that if you can dream up a use-case, there is most likely a built-in function which can do what you need.

Here, I have combined all of my Excel-related posts to date to make it easy to find what you might need. More will follow. If you have a specific request, let me know, and I will do my best to create a post addressing the topic you are interested in.

This area was created specifically for Mary (you know who you are) to begin with, a friend who has applied herself to the pursuit of learning and personal growth. Mary, you inspire! Hopefully, others will benefit as well.

Links to Excel Articles on this blog:

More links will follow, including some to useful resources outside this site. Lastly, remember, for all things related to tech, programming, and math functions, Google is your friend. Use it!

             

            Posted on November 16 2012 09:11 AM by John Atten     

            Comments (0)

            Logical Functions in Excel Part II – AND and OR

            Posted on November 16 2011 09:44 PM by John Atten in Excel Basics, Microsoft   ||   Comments (0)

            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 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 uninitiatedblack_beauty_custom_les_paul_gibson_HQ

            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!):

            AND Function 1 Before Enter

             

             

            Then we hit enter, and voila:

            AND Function 1 After Enter

             

             

            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:

            AND Function 1 After Paste

             

             

            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 . . .

            FenderStrat_04 SmallNow 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:

            OR Function 1 Before Enter

             

            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:

            OR Function 1 After Enter

            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.

             

            Posted on November 16 2011 09:44 PM by John Atten     

            Comments (0)

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

            Posted on October 8 2011 12:18 AM by John Atten in Excel Basics, Microsoft   ||   Comments (0)

            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:

             

            Function Helper Drop Down (SUM)

             

             

             

             

             

             

             

             

             

             

             

            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:

            Syntax Helper Drop Down (SUM)

             

             

             

             

             

             

             

             

             

             

             

            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:

            IF Function Syntax Helper

             

             

             

             

             

            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:

            Silly Happy Function Before Enter

             

             

             

             

            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:

            Silly Happy Function After Enter

             

             

             

             

            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:

            Silly Happy Function After Paste

             

             

             

             

            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 , 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:

            Is Quota Met Before Enter

             

             

             

             

             

             

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

            Is Quota Met After Enter

             

             

             

             

             

             

            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).

            Quota Example Before Enter

             

             

             

             

             

             

            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:

            Quota Example After Enter

             

             

             

             

             

             

            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.

             

            Posted on October 8 2011 12:18 AM by John Atten     

            Comments (0)

            About the author

            My name is John Atten, and my "handle" on many of my online accounts is xivSolutions. I am Fascinated by all things technology and software development. I work mostly with C#, JavaScript/Node, and databases of many flavors. Actively learning always. I dig web development. I am always looking for new information, and value your feedback (especially where I got something wrong!). You can email me at:

            jatten at typecastexception dot com

            Web Hosting by