Functions: Introduction (2 of 2)

We read a brief about Microsoft Excel built in functions in earlier post. Now let's see a bit more of functions and related stuff in this final post on functions introduction.

If you enter incorrect inputs or do not follow the function syntax correctly, the result would be an error. Let's have a look on few common mistakes people do and how to correct those.

Types of Formula Errors

Though you would take precautions, formulas resulting an error is common most of the times. Let's look at common errors that your function results into. Note that the reasons for errors and how to resolve them are situation dependent hence will be different in different cases.

Now that we know the types of errors and what causes them, we can make sure formulas are entered as required. Next time we will see how to check where the errors occurred and how to debug them. See you then!!

Functions: Introduction (1 of 1)

If you are working on Excel for number crunching or analysis like I do, formulas and functions are the most used part of Excel. In fact, functions are what make Excel interesting. Without them, any spreadsheet is just a boring text editor.

What Is?

Excel formula is a way to perform calculations which always returns a value. Input for the function can be text, numbers, mathematical & logical operators, Boolean values, reference to other cells, named ranges and even other formulas.

Functions are built formulas in Excel that eliminates need to write a complex formula.

A formula may or may not contain a function in it. As said above, result of a formula will always be a value (for example, it means you cannot format a cell by using a formula). Result can be a text, number, Boolean. Excel automatically updates results of formulas whenever a dependent cell is changed.

Function should start with a ‘=’ sign however it works even if you enter ‘+’ (plus) or ‘-‘ (minus). The equal sign tells Excel that the succeeding characters constitute a formula.But be careful, a function starting with ‘+’ (plus) and same function started with ‘-‘are not same and returns different results.

Below are some examples of formulas:

Operator precedence

Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur. If you combine several operators in a single formula, Excel performs the operations in the order ^, * and /, + and -, &, =, < and >. If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication and division operator — Excel evaluates the operators from left to right.

To change the excel default order one will have to use a parenthesis (brackets). For example:

Formula ‘=5+2*3 ‘will calculates to 11 and formula ‘= (5+2)*3’ results 21.

How to access

• By typing function in a cell with operator (=, +, -)
• By typing operator in the cell and select a function in name box dropdown
• By typing function in formula bar
• Clicking on insert function button next to formula bar
• From Formulas tab (Excel 2007) or Insert menu (Excel 2003)
• From buttons available in different tabs (Excel 2007)
• From VBA macros
• From shortcut buttons placed

Function Categories

Excel has built in functions grouped under different categories. Below screenshot gives you quick look of categories in Excel 2003.

Here is another screenshot for categories in Excel 2010.

There are two more categories ‘Cube’ and ‘Compatibility’ which are not shown in above picture because of scroll.

Function Syntax

Built in functions or VBA written functions in Excel requires specific way of input in order to work as desired and not to end up with an error. This structure of the function is called ‘syntax’. One must follow the syntax required by the function. Now we shall see the parts of syntax.

1. We now know that every function must be started with ‘=’ (Equal sign).
2. Name: Type function name or select the correct function from the list (examples: SUM, IF, VLOOKUP)
3. Arguments: There are the inputs required by function. Each argument will have to be separated by a ‘,’ (comma, without quotes). Excel function are 2 types from arguments perspective.
• Function that requires no inputs hence no arguments (Ex: Function TODAY does not require any input but returns today’s date)
• Function that require inputs. (Ex: Function IF require 3 arguments: a logical test, value if true and value if false)
• Function that require at least one mandatory input and one or more optional inputs. (Ex: Function VLOOKUP requires 3 mandatory arguments: lookup value, table array, column index and contains 1 optional argument: range lookup). Function will not result in ‘Error’ even if you do not enter an input for optional argument. However since excel might have default values for optional arguments, formula may not return a value you are looking for in this case.
• ** Optional arguments are enclosed with [ ] in function syntax.
4. Parenthesis (brackets) are used to start function arguments and close the function
5. Comma: Arguments are separated by a comma

Let’s see VLOOKUP function syntax to get an visual understanding of above.

You can nest multiple functions and formulas in one cell and create a mega formula. We will see more about this in upcoming posts.

Click here for list of built in functions available in Excel 2003, 2007 & 2010. (Sorted alphabetical)

We shall look more about functions in the future posts.