Guide: Function Usage

After the initial post, I feel it'd be a good idea to step back a bit and take a look at the basics of Excel. To the very beginner, jumping directly into syntax and functions may not be the best approach. I believe that if you found your way to this blog, you are familiar enough with Excel that explaining the general concepts, such as cells and formatting, won't be necessary; however, learning to manipulate functions may be useful.

To begin, we'll start with a function that nearly everybody is familiar with: SUM(). The SUM() function is one of the simplest functions in that it takes nearly any kind of input. A function is designed to take input and return something back to you. If you give SUM() an input of 1, it will return 1. You can try it out in Excel by typing in:

=SUM(1)

This is fairly useless for anybodies purpose, though. To do something useful, we need to give it more arguments. In Excel, when we talk about arguments we are talking about the input to a function. It looks something like this:

=SUM(argument_1, argument_2, argument_3, argument_4)

This example includes four arguments separated by commas, this is the standard method of passing information into a function. Cell references, numbers, names, ranges, other functions, or even strings can be passed into a function -- as long as the function is expecting it. SUM() cannot accept strings and if you try to pass it a string it will return #VALUE! meaning, which is Excel's way of saying that you gave a function a data type that it wasn't expecting.

A cell reference is simply a reference to another cell. If you type "Hello," in B1 and " world!" (without quotes) in C1 then =B1 & C1 in cell A1 then Excel will take the value of B1 and append (which is what & does) C1 onto the end returning a value of "Hello, world!" to A1.

There are a couple of ways to pass an argument to a function: you can simply type in the function box or in the cell (figure 1) or you can click on cells, row/column headers, or click-and-drag selection boxes (figure 2).

Figure 1

Figure 2

You can select multiple cells at once by clicking and dragging or select multiple individual cells by clicking them while holding down the Control (CTRL) key. Once your selection is made you can click the check mark between the "x" and "fx" buttons on the function bar (pictured at the top of both figures above) or by pressing enter on the keyboard. If you had a column of numbers in A, you could type in "=SUM(" and then click on the column header A and press enter to sum the entire column.

Tip: SUM() has a very quick keyboard shortcut. If you select the cell above or to the side of the data you wish to sum, you can simply Alt and = together and then press enter, Excel will automatically sum the data as best it can.

A number or a string can also be passed to many functions. A string is anything in quotes, such as HeLlO WOrlD! in =PROPER("HeLlO WOrlD!"), that is passed to a function and a number is any number passed in quotes or out of quotes (Excel does its best to guess at what you want, if you try to add "1" and "2" it will guess that you mean 1 and 2 and return 3, it will NOT work like this, though, "one" and "two").

Ranges can be passed just like cells, numbers, or strings. You can type in the range such as: A:A selects the entire A column, A:B selects both columns A and B, A1:B15 selects all cells inside the "box" created between A1 and B15. Likewise, you can simply click and drag the selection you'd like.

Named ranges, or names, can be passed to functions as well. These have two purposes: dynamic cell selection (which we'll cover another day) and user-friendly readability. You can name any cell or any selection of cells by simply selecting them and entering the name you wish to the left of the function bar (red arrow in figure 3).

If you were to type "BeeTwo" over "B2" in the Name Box, you could reference that cell as "BeeTwo" from that point on. This is extremely useful for other people that use your spreadsheets to be able to understand what you've done.

Figure 4 illustrates in a very simple way how this can be useful. By naming the value for total orders ORDERS and the value that contains the number of days those orders cover DAYS we can write in another cell the following function: =ORDERS / DAYS. This function makes it very simple to the next person to use this spreadsheet to say that this cell takes the number of orders and divides it by the number of days. This is not evident if you simply typed =A2 / A3. Naming cells or ranges is something to keep in the back of your mind but it is not common to name everything.

Figure 4

That about sums up how to pass arguments to functions. The concepts here can be applied in a variety of ways to get very interesting and useful results.