Excel In Your Office: 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.

We now know that every function must be started with ‘=’ (Equal sign).
Name: Type function name or select the correct function from the list (examples: SUM, IF, VLOOKUP)
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.

Parenthesis (brackets) are used to start function arguments and close the function
Comma: Arguments are separated by a comma