Microsoft Office tips

Select Articles

Excel Tips Part II: Formulas and Functions

Published Wed, 24 Jun 2009 17:50:00 GMT

To conclude this two-part series, we'll focus on function. Having covered formula basics last week, functions can be covered in pretty short order.

Excel has several hundred functions, though the most commonly used are sum, average, count, and the like. The most basic definition of a function is it's a mathematical short cut. A more technical definition is an operation or series of operations that return a single value. Although this definition may leave some uncertainty of what a function does, it does help to cover the broad range of Excel functions, that don't only perform mathematical operations, but also address functions that perform date and time operations, database an lookup activities and logical evaluations.

As mentioned in the last article, functions exist in an Excel formal as an operand. Put differently, they are a section or part of a formula. Formulas start with an equal sign, a function can be an operand anywhere with in the formula. Often it is the first operand, and commonly, it may be the only operand.

Syntactically, functions start with the name of the function, like sum and always have a pair of parenthesis. Arguments go between the parenthesis--maybe. Some functions have no arguments, such as the now functions. It's syntax is NOW(), and returns the current date and time. (Excel does not use case, however I'll use it here for clarity). Other functions may accept 1 argument, or 4 arguments. Some functions have optional arguments. For example, many people are familiar with the sum function, SUM(A1:B2), which adds all of the arguments together. What many people do not know is that the sum function allows up to 30 arguments. Arguments are separated by commas, so to expand on the last example, the following is a legal sum expression: SUM(A1:B2, D1:E2).

This is probably a good place to through a reminder from the last article: Remember, operands are separated by operators. Arguments are separated by commas. This is a key rule to understanding complex formulas.

In further review, in the previous article, I listed the possible operand in an Excel formula.

Literals - for example 3 or "hello"
Cell References - like B4 or R19
Functions - such as Sum() or Average()

The list of arguments in a function are very similar:

Literals - again, like 3 or "hello"
Cell References - like B4 or A9:C24
Expressions - such as 3 + 4

There are a couple of important things to note about this list. Starting with literals, of course not all functions allow text, but there are several that do.

In formulas, a cell reference can only refer to a single cell. However, many functions allow a range of cells as a single argument. In the SUM(A1:B2,D1:E2) example, all cells in the first range will be added together, then all the cells in the second one will be summed, then both results will be added together to provide one some of all the cell values.

Finally, an expression is essentially an Excel formula in the middle of an bigger formula (consequently, it lacks the leading equal sign). As an expression, it must obey the operand operator operand syntax of a function, and can include all of the legal operands listed above.

The end result of that means the possibilities of formulas inside functions inside function inside formulas. And that's what it's imperative to understand that operands are always separated by operators and arguments are always separated by comma.

Those are the essentials of Excel formulas and functions. Briefly: understand the syntax rules of both, and know your orders of operations, and creating even the most complex calculations should be just a little simpler.

Microsoft Office Tips are extracted from our blog. All articles from the blog are available at blog.optimal-it-solutions.com.

Want to learn more? Contact Us

“My experience has shown me that the people who are exceptionally good in business aren't so because of what they know but because of their insatiable need to know more.”
- Michael E. Gerber
Want more quotes?

Site Map

Tags: search engine optimization, seo, google ranking, search engine placement