Excel has a library of functions that allows you to quickly make specific calculations. Functions in Excel take arguments. Most functions will require at least one argument. Arguments can be a range of data, a specified output, or other parameters, depending on the particular function. Arguments are typically presented in parentheses following the function.
Here are a number of common functions that you are likely to use:
- Average(number1, number2) - calculates the average (mean) of a range of cells
- Stdev(number1, number2) - calculates the standard deviation of a range of cells
- Sum(number1, number2) - adds all of the values of a range of cells
- Count(value1, value2) - counts the number of items in a range of cells (blank cells will not be counted & neither will cells that are formatted as text)
- Median(number1, number2) - calculates the median of a range of cells
- Mode(number1, number2) - calculates the mode of a range of cells
- Max(number1, number2) - gives the maximum value of a range of cells
- Min(number1, number2) - gives the minimum value of a range of cells
- If(logical_test, value_if_true, value_if_false ) - allows one to perform a logical test (e.g. IF A1 >= 5, then 0 if true, or else 1 if false)
This is not an exhaustive list, by any means. We'll talk more about finding functions in the section below, (which you can jump to here) but first we should go over how to use functions in Excel.
For example, let's say you wanted to take the average of the increase in population of the first ten census tracts which you would need some sort of logical argument for doing (such as demonstrating a function for a tutorial). Click on the cell where you want the average to appear. Type '=' (because without an 'equals,' the function won't run), then type average, and then the open parenthesis. Excel will prompt you for the arguments.
The required arguments are specified as 'numbers', however you can also use cell referents, so long as the cells are formatted as Number or General. The square brackets [ ] around an argument mean that the argument is optional. For the average function you must have at least one cell referent (or number) to average (of course, the average of a single value isn't very interesting- what's the point of calculating it really?). When entering arguments you can enter each one and separate them with a comma, as the argument list is presented below. This would be quite time consuming if you have a ton of numbers to average. You can also select the entire range of cells that you want to average with the mouse.
You can also type "G9:G18" if you prefer to remain at the keyboard instead of switching back and forth between the keyboard and the mouse.
In the formula bar we see the function and in the cell we see the output. Let's walk through another example using the sum function.
For example, let's say a small grocery store (or maybe your local bodega) was interested in how much customers spent on eggs and milk each day in a given week. After setting up the headers...
Insert the data.
We can also format the entered data by selecting the '$' in the Number group. We can extend the formatting to the Total cells too.
We can also widen column A so that all of Wednesday isn't clipped off. But finally, let's use a function!
In the Total column, we want the sum of the amount spent on eggs and milk. In cell D2 we type "=sum". As we type, Excel will give us a number of prompts for possible functions.
We're sticking to the basic sum function, so we'll continue by typing the open parenthesis. Then select cells B2 & C2 with the mouse (or by typing them).
Notice that the function also appears in the formula bar. Then we can either type the ending parenthesis or simply press ENTER. Typically, we don't need to type the end parenthesis, because Excel will automatically add it for us, UNLESS we are writing a formula instead of using one Excel provides (or if we have a mistake in our argument list. We'll go over errors on the next page). After pressing ENTER we see this:
Now we want to sum the information for the rest of the days of the week. We can repeat what we just did: type "=sum(" and then select the cells, OR we can use Auto Fill. Remember that? Click on the cell with the function (in this case D2) and move the mouse over the bottom right corner until it makes a plus sign, and then click and hold down the mouse to select all the cells into which you want to copy the function. You'll see a blue outline showing the cells that are being selected.
When you release the mouse, those cells will have the function copied into them, BUT changed to refer to the cells in each row. For example, look at D3 in the formula bar.
Essentially, Excel is copying: Dx = Bx + Cx, where x is the row number. You can also use Auto Fill horizontally. In this particular example, it doesn't make sense, but just to see how it works: select the cell with the function (in this case D2, again) and move the mouse over the bottom right corner to get the plus sign. Then click and hold the mouse down and drag to the right. You'll see the blue outline highlighting the selected cells.
When you release the mouse, those cells will have the function. See E2 in the formula bar?
In this case, instead of copying: Dx = Bx + Cx, Excel is copying Y2 = (Y-2)2 + (Y-1)2, where Y represents the column letter and Y-1 represents one column letter to the left of Y, etc. To translate that- the columns change, not the rows. E2 is the sum of the cost of milk (C2) and the total cost of eggs and milk (D2), so in this dataset, summing this way makes absolutely no sense, but it is good to know how to do because there are datasets where it will make sense to Auto Fill horizontally instead of vertically.
When I delete E2, I get this:
Can you figure out why $96.09 repeats in F2 & G2? Click here to find out if you are right.
For more possible functions, check out this external link to Microsoft which lists a variety of functions categorized based on their potential usage found in Excel 2010.
Additionally, you can search for functions in Excel. Go to the Formula tab and click on Reference. You will see a wide array of categories. Clicking on any of these categories will provide you with a list of related functions.
Excel for Mac nicely includes a description of what the function actually does beside its name.
You'll need to know the arguments necessary for each new function. This is presented in the parentheses. Even when Excel says that the function takes a number you can use a cell referent with a number, as opposed to entering the specific number. Remember all the reasons it is better to use a cell referent for a formula? (If not, go back to this page.) The same reasons apply to functions.
To select a function, click "Formula Builder." When you select the function (in this case we are looking at the ABS function which will give us the absolute value of a number) it will allow you to enter the appropriate arguments at the bottom of the dialogue box. This can make navigating a new function much easier.
We are entering the function in cell B1 and will use cell A1 as the argument. You can either click on the cell or you can type 'A1' into the argument box.
After clicking OK (or pressing ENTER) we get this:
Notice that the function is visible in the formula bar when we click on B2, but the cell shows the output. As with writing formulas, if you want to edit the function, you can do so in the formula bar or by double clicking on the cell.
And don't forget: no 'equals' equals no function!
Some Excel versions for Mac don't include the "Function Wizard" command, which allows you to search for a function by typing a brief description of what you want. However, you can use Formula Builder to search.
For example, I might want to count a series of numbers. Beside the magniying glass, I type "count" and it brings up the "COUNT" function which does this.
When you single click on a function, the required arguments and a description of what it does appears below the box. You can also get additional help with a function (if you are connected to the internet) by clicking on the Help with this function link in the bottom left corner. Once you decide on the function you want, click OK and it will open the Function Arguments dialogue box that we saw when we were exploring the ABS function above.
Which function do you think would best be able to count the number of zeros in a set of data? Click here to find out.