Excel Basics Consolidated

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:

• Microsoft Excel Basics: An Introduction to the Excel Workbook for Beginners
• Microsoft Excel Basics Part I
• Microsoft Excel Basics Part II: Data Types in Excel
• Excel Basics Part III: Expressions, Functions, and Formulas
• Excel Basics Part IV: Number Formats
• Logical Functions in MS Excel: IF, AND, and OR
• Logical Functions in Excel Part II – AND and OR

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!

 

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.

 

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

 

Excel Basics Part III: Expressions, Functions, and Formulas

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

Spreadsheets are misunderstood by the majority of people who use them. For many people a spreadsheet is nothing more than a handy way to create and organize lists. Some people use spreadsheets to present and layout data in a pleasing grid-like manner, and to apply pretty colors and formatting. If you are one of these people, you are missing out, and most likely working much harder than you need to.

Microsoft Excel, and other spreadsheets, are powerful data analysis tools. First and foremost, they are purpose-built to crunch numbers, twist, and manipulate information. A properly set up spreadsheet can make fast work out of calculations which would be laborious a few decades ago. While not really intended for this purpose, a spreadsheet can also serve as a small ad hoc database if no better alternative is available.

For even the most casual of users, properly understanding how a spreadsheet works, and how it handles the information you put into it will provide an almost immediate payoff. You do not have to be a "computer whiz" to get impressive results. Most importantly, you can save yourself time and pain by letting the spreadsheet do the heavy lifting for you. But before you can do that effectively, it is important to understand how Excel stores and works with information.

 

What's an Expression?

Cells in a Microsoft Excel Spreadsheet may contain data, or they might contain an expression. Data is a value you type or otherwise place into a cell, such as Text, Numbers, Etc. (See Previous post in this series, Microsoft Excel Basics Part II: Data Types in Excel). The cell represents the actual value entered (despite any formatting which may cause it to display differently).

An expression is a statement or formula which returns a value. Further, an expression or formula might contain variables, meaning that the value returned by the expression might change depending upon the values provided as variables. If this is a little foggy for you, I will do my best to clarify in a minute. The point here is that, while within the cell, the formula is evaluated/computed what have you, from the spreadsheet perspective, the cell still represents a VALUE – the value returned by the formula.

While some of this becomes difficult to explain without creating even more confusion, expressions, formulas, and functions are what give Excel its real power. So this stuff is important to understand if you are to do anything with excel beyond keeping lists and laying out pages.

Here is an really simple example of a formula with one variable from every day life:

Let's assume that you and a group of your friends are going to see a movie. Since you are the well-heeled benefactor of the group, you are paying for everyone's movie ticket (If this is the type of person you are, shoot me an email – we should hang out. A lot.). The price of each movie ticket is $10.50 (yes, I really paid that much to go see a movie recently). How much will your total bill be?

Notice how you can't quite answer that question yet? We know how much tickets cost (at THIS theater, anyway), but we don't know how many of your friends are going yet, therefore we can't know how much the total cost will be. However, we DO know that your final cost might be expressed as follows:

Total Cost = $10.50 X Number of Tickets Needed

In constructing the above (overly simple) formula, we determined the following:

• What we WANT to know (our desired output, if you will) is the Total Cost.
• What we already know is the cost per ticket.
• What we don't know is the number of tickets we will be buying, which depends upon how many friends come to the movie with us.

Now, while everyone has different degrees of math aptitude, I will assume that, if you are reading this post, you would probably have mentally dealt with this, and performed the necessary computation in your head or with a calculator without breaking things down this far. But in essence, you have still mentally employed a formula.

Now let's take our example a step further. Different theaters charge different prices for tickets. In fact, the same theater might charge a different price for a ticket depending on the time of day (the so-called matinee price). If we did not yet know where we planned to see the movie, we would introduce another unknown into our formula:

Total Cost = Ticket Price X Number of Tickets Needed

Now we have formula with TWO variables, Ticket Price and Number of Tickets Needed.

As we demonstrated in Part I of this series, we can use Microsoft Excel to perform simple calculations like this right in the cell. Let's say we have determined that you will be taking 5 of your friends to see the matinee showing of Rise of the Planet of the Apes at a theater which charges $8.50 per ticket. You COULD do the following, which is kind of like using Excel as a calculator (remember to type the "equals" sign as the first part of any calculation or formula!):

Before you hit Enter:	After you hit Enter:

Note that we entered the value for Price per Ticket times the value of Number of Tickets Needed, just as our formula indicates. When we hit Enter, the result (the Total Cost) of 42.5 is displayed in the cell. For the purpose of Excel, the value displayed in the cell IS the Total Cost element in our formula above. This is all well and good for quick and dirty "one-off" calculations. But what if we wanted to be able to check the total cost for any number of friends, at any theater?

We can probably use Excel to come up with a more useful way of doing this. Let's try this instead. We will create a Column Header for the Ticket Price,and another for Number of Tickets Needed. These can represent the variables in our formula. Then we will add a third column, Total Cost, which will represent the output of our formula. In THIS cell, we will re-write our formula using references to the values in the other cells:

Before you hit Enter:	After you hit Enter:

With THIS model, we can test different combinations of Price per Ticket and Number of Tickets Needed simply by typing new numerical values into the appropriate column. For example, if at the last minute your friend Jodi decided to go along, you would need six tickets instead of five. Simply enter the new value into the Number of Tickets Needed cell and hit enter:

Before you hit Enter:	After you hit Enter:

Voila! You now know that if the Number of Tickets Needed is six instead of five, the cost will be $51.00 instead of $42.50.

Your comfort level with creating your own formulas in Excel will depend to some degree upon your familiarity with basic algebra and other math. However, even if you have forgotten more math than you remember, it does not need to stop you from getting the most out of Excel. Some of the most commonly needed math operations are built in to Excel as functions.

What's a Function?

A function is a bit like a formula, in that a function can use a combination of known values and variables, perform a calculation, and produce a result which can then be displayed, or used within OTHER functions or formulas. The difference between a formula and a function is that a function can be referenced by its name and will return the value of the calculation contained within. Confused yet?

Way back in the day, when I attended high school (up hill both ways, in the snow, etc. . . .), we were introduced to functions by way of the Function Machine. We will discuss that in a minute, but I am going to step out even further, because the function machine analogy was hard for me to grasp at first, simple though it is.

We'll start with something a little more concrete. Let's say you own a printer made by a particular manufacturer (say, Hewlett-Packard), which through bad luck or negligence, has ceased working properly. You decide you are going to get it repaired. You locate a printer repair shop which specializes in repairing HP printers, and which indicates the cost for repairs is a flat $50.00 (we're keeping this simple).

What you want to do is drop your printer off, pay the man his $50.00, and leave with a working, repaired printer.

If we were to examine this situation from the perspective of a function machine, we might say that the printer repair shop is a Printer Repair Machine. You want to put your Broken Printer plus some money IN, and get back OUT a Working Printer. You are not real interested in what happens WITHIN the printer repair shop, how the man dissembles the printer, or what needs to be done to restore the printer to working order. What you want is a working printer.

We could break all that down like this:

The printer repair machine accepts input known as parameters or arguments, which must include a BROKEN PRINTER and $50.00. Upon receiving those inputs, the Printer Repair machine does one thing: PERFORM REPAIRS. When this action is complete, the printer repair machine provides the expected output, a WORKING PRINTER.

The printer repair machine itself is a bit like a black box. We don't necessarily know what is going on when it does the PERFORM REPAIRS action, we just know that, barring any unexpected problems (or, error conditions), we can expect to receive a WORKING PRINTER when the printer repair machine is done.

About those unexpected problems. This carries a nice analogy as well. What if we brought in a broken bicycle instead? This might be regarded as an error. The shop would report that they cannot do the PERFORM REPAIR activity on a bicycle. Another example of unexpected problems might be if you only provided $40.00 instead of the required $50.00. In this case, the repair shop might kick out a message to you letting you know you have not provided enough money.

In the case above, we have a function Machine which performs a function: PERFORM REPAIRS. In order for PERFORM REPAIRS to execute properly, there are two required inputs, or parameters we need to supply: A BROKEN PRINTER, and MONEY ($50.00). If we put those two things into the Printer Repair Shop, the PERFORM REPAIR function is executed, and we receive a happy WORKING PRINTER as an output.

We could express this another way:

Working Printer = PERFORM REPAIRS(Broken Printer, Repair Fee)

In the above expression, the PERFORM REPAIRS function accepts the two required parameters and returns a Working Printer. In this syntax, the name of the function is used, followed by the parameter values, in a specific order, between the parentheses. The parameter values are separated by a comma.

Before I relate this back to functions in Excel, lets take things one step further. Let's say that the printer repair shop will expedite the repair operation in under 24 hours for an additional fee of $10.00. Without the extra $10.00, the shop will only guarantee repairs within 5 working days. This is optional, for those customers who are in a hurry, and wish to pay a little extra to get the work done faster.

Now our diagram might look like this:

In THIS case, the PERFORM REPAIRS function requires at least the first two input parameters; a Broken Printer and $50.00. If the customer opts for the expedited repair option, the function will also accept an input of $10.00 and guarantee completion within 24 hours. Otherwise, the Working Printer output will be done in five working days.

In this case, we might use the following notation to express our function:

Working Printer = PERFORM REPAIRS(Broken Printer, Repair Fee, [Expedited Repair Fee])

In the above expression, the optional fee is notated in square brackets, indicating its optional status.

Excel Built-in Functions

SUM Some Values

Microsoft Excel has a plethora of built-in functions, in many different categories. To get us started, we will examine a few of those most commonly used for everyday spreadsheet tasks.

Not surprisingly, addition is probably THE most common thing we do in a spreadsheet. When we add the values represented by two or more cells, it can be tempting to write an expression like this:

Note that we are adding the values in the four cells above the current cell. However, there is a more efficient way to do this, using the built-in function SUM().

Remember, a function is a way to use a pre-defined formula or operation by using the name of the function, and providing any required (or sometime optional) parameters. In this case, the most basic function Excel provides us is called SUM, which, not surprisingly, returns the result of adding one or more numbers together.

One way of using the SUM function is to provide a range of values as a parameter. It is no coincidence that I use the term range here. Remember from Part I of this series that a group of cells is also called a range? For example, in the example above, I am adding the values contained within the range of cells represented by cell A1 thru cell A4. Another way to write this is the range represented by A1:A4, where we separate the top left cell address and the bottom right cell address with a colon:

So in reality, our expression could be written like THIS:

Before you hit Enter:	After you hit Enter:

Wasn't that a little more convenient? Also, note that while you CAN manually type the range into the cell, you can also use the following procedure, which can save a lot of time when you are doing a lot of "summing" over large ranges:

• Type the first part of the formula: =SUM(
• Use the mouse pointer to select the range you want to add by clicking on the first cell, and drag the mouse (while holding the mouse button down) across the range of cells to the last.
• When you let up the mouse button, the range you have selected will be surrounded by the "running ants" outline, and the address of the first and last cells of the range will be entered in your formula.
• Type the closing paren and hit enter.

1. Type Formula:	2. Select First Cell:

3. Drag Down:	4. Close Paren:

5. Hit Enter:

It was harder to read through all those images than it was to make THAT formula happen, eh?

You can use the SUM function across any number of cells, within a single column or row, or spanning multiple columns and rows. For example, you could do THIS:

Block Range (Before Enter):	Block Range (After Enter):

Note that in the above example, we told Excel to use the SUM function to add the values in the range A1:B2. The function correctly returned the value of 24.

As we learned previously, functions can use the results of other functions or calculations as input parameters as well. Let's return to the sales order example from the first post in this series. We had created some column headings, and listed some items we might purchase, and quantities for each. Recall that we used a simple formula to calculate the total cost for the specified quantity of each item:

Now we already know that we can use the SUM() function to add together the calculated item totals to arrive at a grand total for the order, because as we learned earlier, a formula or function can use the results of other formulas or functions as part of an additional calculation. So we could go ahead and type our SUM function into the cell just below the item totals to produce our grand total:

Here is the same Sales Order example again, with the values displayed instead of formulas, and with the SUM() function typed into the appropriate cell:

Sales Order Grand Total (Before Enter)	Sales Order Grand Total (After Enter)

Another commonly used function which accepts a range as an input parameter is AVERAGE(). It does just what it sounds like – returns the average of a range of values. We type the name of the function, and then enclose the range we wish to average as a parameter within the parenthesis.

=AVERAGE(Some Range of Cells)

Let's take a quick look at the average function. Let's say that four salesmen have posted their stats for the quarter. We want to examine the average sales volume per sales person for statistical purposes. We could make a column named Sales Person and list each salesman's name within that column. Then we could create a column Names Sales Volume in which we can list each salesman's actual figure for the quarter. Then, using the AVERAGE() function, we can determine the average sales volume per sales person:

Average Sales Volume (Before Enter):	Average Sales Volume (After Enter):

Ok. We have examined two commonly-used, but fairly rudimentary functions provided as part of Microsoft Excel's built-in function set. In the next post, we will look at where to find an entire library of functions, and we will examine some more advanced functions and possible uses within a spreadsheet. After that, we will devote an entire post (or maybe two!) to formatting, and how formatting might affect what you see on screen as the result of certain functions.

Summary

• An expression is a formula or statement which returns a value.
• A function is a formula we refer to by name within an expression, which accepts parameters (sometimes called arguments) as input, and returns a value. The value of the output will be affected by the parameters provided as input(s).
• The SUM() Function can be used to add a range of values provided as an input parameter.
• The AVERAGE() function can be used to return the average of a range of values provided as an input parameter.

John on Google