Functions in Excel 2016 Formulas - dummies

By Ken Bluttman

Functions in Excel 2016 are like little utility programs that do a single thing. For example, the SUM function sums numbers, the COUNT function counts, and the AVERAGE function calculates an average.

There are functions to handle many needs: working with numbers, working with text, working with dates and times, working with finance, and so on. Functions can be combined and nested (one goes inside another). Functions return a value, and this value can be combined with the results of another function or formula. The possibilities are nearly endless.

But functions do not exist on their own. They are always a part of a formula. Now, that can mean that the formula is made up completely of the function or that the formula combines the function with other functions, data, operators, or references. But functions must follow the formula golden rule: Start with the equal sign. Look at some examples:

Function/Formula Result
=SUM(A1:A5) Returns the sum of the values in the range A1:A5. This is an
example of a function serving as the whole formula.
=SUM(A1:A5) /B5 Returns the sum of the values in the range A1:A5 divided by the
value in cell B5. This is an example of mixing a function’s result
with other data.
=SUM(A1:A5) +
AVERAGE(B1:B5)
Returns the sum of the range A1:A5 added with the average of
the range B1:B5. This is an example of a formula that combines the
result of two functions.

Ready to write your first formula with a function in it? Use the following steps to write a function that creates an average:

  1. Enter some numbers in a column’s cells.

  2. Click an empty cell where you want to see the result.

  3. Type =AVERAGE( to start the function.

    Note: Excel presents a list of functions that have the same spelling as the function name you type. The more letters you type, the shorter the list becomes. The advantage is, for example, typing the letter A, using ↓ to select the AVERAGE function and then pressing the Tab key.

  4. Click the first cell with an entered value and, while holding the mouse button, drag the mouse pointer over the other cells that have values.

    An alternative is to enter the range of those cells.

  5. Type).

  6. Press Enter.

If all went well, your worksheet should look a little bit like the following figure. Cell B10 has the calculated result, but look up at the Formula Bar, and you can see the actual function as it was entered.

Entering the AVERAGE function.
Entering the AVERAGE function.

Formulas and functions are dependent on the cells and ranges to which they refer. If you change the data in one of the cells, the result returned by the function updates. You can try this now. In the example you just did with making an average, click one of the cells with the values and enter a different number. The returned average changes.

A formula can consist of nothing but a single function — preceded by an equal sign, of course!